本周我学到的最令人惊讶的事情是在ORDER By子句中使用CASE语句。例如:

declare @orderby varchar(10)

set @orderby = 'NAME'

select * 
    from Users
    ORDER BY 
        CASE @orderby
            WHEN 'NAME' THEN LastName
            WHEN 'EMAIL' THEN EmailAddress
        END

删除到数据库的所有连接:

Use Master
Go

Declare @dbname sysname

Set @dbname = 'name of database you want to drop connections from'

Declare @spid int
Select @spid = min(spid) from master.dbo.sysprocesses
where dbid = db_id(@dbname)
While @spid Is Not Null
Begin
        Execute ('Kill ' + @spid)
        Select @spid = min(spid) from master.dbo.sysprocesses
        where dbid = db_id(@dbname) and spid > @spid
End

使用GETDATE() +或-来计算附近的日期

SELECT GETDATE() - 1 -- yesterday, 1 day ago, 24 hours ago
SELECT GETDATE() - .5 -- 12 hours ago
SELECT GETDATE() - .25 -- 6 hours ago
SELECT GETDATE() - (1 / 24.0) -- 1 hour ago (implicit decimal result after division)

好吧，这是我的观点:

http://dbalink.wordpress.com/2008/10/24/querying-the-object-catalog-and-information-schema-views/

我懒得在这里重新写一遍，所以请查看我的帖子。这对许多人来说可能是微不足道的，但会有一些人会发现它是一颗“隐藏的宝石”。

编辑:

过了一会儿，我决定在这里添加代码，这样您就不必跳转到我的博客来查看代码了。

SELECT  T.NAME AS [TABLE NAME], C.NAME AS [COLUMN NAME], P.NAME AS [DATA TYPE], P.MAX_LENGTH AS[SIZE],   CAST(P.PRECISION AS VARCHAR) +‘/’+ CAST(P.SCALE AS VARCHAR) AS [PRECISION/SCALE]
FROM ADVENTUREWORKS.SYS.OBJECTS AS T
JOIN ADVENTUREWORKS.SYS.COLUMNS AS C
ON T.OBJECT_ID=C.OBJECT_ID
JOIN ADVENTUREWORKS.SYS.TYPES AS P
ON C.SYSTEM_TYPE_ID=P.SYSTEM_TYPE_ID
WHERE T.TYPE_DESC=‘USER_TABLE’;

或者，如果你想拉出所有的用户表，像这样使用CURSOR:

DECLARE @tablename VARCHAR(60)

DECLARE cursor_tablenames CURSOR FOR
SELECT name FROM AdventureWorks.sys.tables

OPEN cursor_tablenames
FETCH NEXT FROM cursor_tablenames INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE],   CAST(p.PRECISION AS VARCHAR) +‘/’+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM AdventureWorks.sys.objects AS t
JOIN AdventureWorks.sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN AdventureWorks.sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.name = @tablename
AND t.type_desc=‘USER_TABLE’
ORDER BY t.name ASC

FETCH NEXT FROM cursor_tablenames INTO @tablename
END

CLOSE cursor_tablenames
DEALLOCATE cursor_tablenames

其他参考资料(我的博客):http://dbalink.wordpress.com/2009/01/21/how-to-create-cursor-in-tsql/

在SQL Server 2005/2008中显示SELECT查询结果中的行号:

SELECT ( ROW_NUMBER() OVER (ORDER BY OrderId) ) AS RowNumber,
        GrandTotal, CustomerId, PurchaseDate
FROM Orders

ORDER BY是一个强制性条款。OVER()子句告诉SQL引擎对指定列(在本例中为OrderId)上的数据进行排序，并根据排序结果分配数字。

如果你想知道表的结构，索引和约束:

sp_help 'TableName'