1.Common Table Expression：
CTE语法定义：
WITH
AS
(
)
/**//*
定义多个CTE时，用逗号隔开，后一个CTE可使用前一个CTE
[
，
WITH
AS
(
)
]
*/
SELECT <字段名称> FROM
WITH Sales_CTE
AS
SELECT * FROM Sales使用CTE的好处是在同一个批中，只要一次定义，就可以多次使用，相比衍生数据表，性能更佳
CTE真正强大之处在于可以做到递归访问数据，当CTE的定义包含自己的名称时，便形成递归。
语法如下：
WITH
AS
(
SELECT <字段名>
FROM <表名>
WHERE <表名>.<字段名>=<值>
UNION ALL
SELECT <字段名>
FROM
ON
WHERE <表名>.<字段名>=<值>
AND lvl<3--通过lvl可以限制仅递归多少层
)
为了避免递归层次过深，耗费资源过大，可搭配OPTION(MAXRECURSION<层数>)查询提示，如下：
SELECT * FROM CarParts
OPTION(MAXRECURSION 50)
2.SQL Server 2005 新增了数个关于记录在数据集合中的关于排序（Ranking）的函数，如：RANK、 DENSE_RANK、 ROW_NUMBER 和 NTILE。
RANK:排名可能不连续，例有两个第一名，则下一个名次直接跳到第三名
DENSE_RANK:与RANK相似，但排名是连续的
ROW_NUMBER:集合中的顺序位置
NTILE:依照指定的分组数量将结果集切分，并显示记录在各组的排名位置
语法定义如下：
OVER ([PARTITION BY <提供数值的描述方式>,[n]]
ORDER BY <字段> [ASC | DESC][,[n]])如：
ROW_NUMBER
SELECT
c.Name AS 类别,
b.Name AS 书名,
b.Price AS 价格,
b.Description AS 描述,
ROW_NUMBER() OVER(PARTITION BY c.Name ORDER BY c.Name,b.Price) AS 行号
FROM Book AS b
JOIN
Category AS c
ON b.CategoryID=c.CategoryID
ORDER BY 类别,价格 desc
GORANK和DENSE_RANK
SELECT
c.Name AS 类别,
b.Name AS 书名,
b.Price AS 价格,
b.Description AS 描述,
/*DENSE_*/RANK() OVER(PARTITION BY c.Name ORDER BY c.Name,b.Price DESC) AS 行号
FROM Book AS b
JOIN
Category AS c
ON b.CategoryID=c.CategoryID
ORDER BY 类别,价格 DESC
GO
NTILE
SELECT
c.Name AS 类别,
b.Name AS 书名,
b.Price AS 价格,
b.Description AS 描述,
CASE NTILE(2) OVER(PARTITION BY c.Name ORDER BY c.Name,b.Price DESC)
WHEN 1 THEN N'高价位'
WHEN 2 THEN N'低价位'
END AS 价位
FROM Book AS b
JOIN
Category AS c
ON b.CategoryID=c.CategoryID
ORDER BY 类别,价格 DESC
GO
3.结构化错误处理
语法如下：
BEGIN TRY
{sql语法 | BEGIN END语句块}
END TRY
BEGIN CATCH
{sql语法 | BEGIN END语句块}
END CATCH可能通过以下几个系统函数取得错误相关信息：
ERROR_NUMBER：错误代码
ERROR_SEVERITY：严重级别
ERROR_STATE：状态代码
ERROR_MESSAGE：错误信息
如：
BEGIN TRY
DECLARE @a INT
SET @a='a'
END TRY
BEGIN CATCH
PRINT '错误代码:'+CONVERT(VARCHAR(10),ERROR_NUMBER())
PRINT '错误严重程度:'+CONVERT(VARCHAR(10),ERROR_SEVERITY())
PRINT '错误状态:'+CONVERT(VARCHAR(10),ERROR_STATE())
PRINT '错误信息:'+ERROR_MESSAGE()
END CATCHRAISERROR函数用于引发错误，如：RAISERROR(@Msg,@Severity,@State)
XACT_STATE()系统函数用来判断当前连接当前事务的状况，以确定是否可以完成或恢复事务，这个函数返回三种值：
0：表示当前没有打开事务
1：表示当前有事务，且你可以完成或恢复该事务
-1：表示只能ROLLBACK，并不能再继续完成事务，只能恢复
4.APPLY操作符：让 SELECT 查询语法可与返回数据表的函数 TVF （Table-Valued Function）做进一步结合。这在数据表含有XML字段时，结合XML的内容转换为新的数据表字段颇为方便。
其格式有两种类型： CROSS APPLY 和 OUTER APPLY 。CROSS 返回在TVF中有对应记录的行，OUTER 返回所有行，因此在OURTER结果集的字段中有可能出现NULL值。
如：
SELECT c.Name AS CategoryName,b.Name AS BookName,b.Price,b.Description
FROM Category AS c
CROSS APPLY dbo.GetBooks(c.CategoryID) AS b--dbo.GetBooks是一个用户自定义函数，返回数据表
WHERE c.Name in('Java','C#','Oracel')
GO
5.PIVOT语法提供类似枢纽分析表的功能，可以将某个字段的值作为新表的字段，并填入汇总数据，如：
SELECT CustomerID AS [客户编号],
ISNULL([1],0) AS [一月],
ISNULL([2],0) AS [二月],
ISNULL([3],0) AS [三月],
ISNULL([4],0) AS [四月],
ISNULL([5],0) AS [五月],
ISNULL([6],0) AS [六月],
ISNULL([7],0) AS [七月],
ISNULL([8],0) AS [八月],
ISNULL([9],0) AS [九月],
ISNULL([10],0) AS [十月],
ISNULL([11],0) AS [十一月],
ISNULL([12],0) AS [十二月]
FROM SalesOrderTotalsMonthly AS s
PIVOT(SUM(SubTotal)
FOR OrderMonth
IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS a
GOUNPIVOT则相反，如：
SELECT * FROM YearlySalesPivot
UNPIVOT(SubTotal FOR OrderMonth IN([1],[2],[4],[6])) AS a
ORDER BY CustomerID
GO
6.OUTPUT语法，在INSERT、DELETE、UPDATE语法中现在可以搭配OUTPUT子句，在对表进行操作的同时，通过OUTPUT子句记录表的操作的相关信息，如：
CREATE TABLE TestTbl(a INT NOT NULL IDENTITY(1,1),b INT)
GO
INSERT TestTbl values(1)
INSERT TestTbl values(2)
SELECT * FROM TestTbl
GO
DECLARE @InsertOutput TABLE(a INT,b INT)
INSERT TestTbl OUTPUT INSERTED.a,INSERTED.b INTO @InsertOutput VALUES(3)
SELECT * FROM @InsertOutput
DECLARE @UpdateOutput TABLE(oldA INT,oldB INT,a INT,b INT)
UPDATE TestTbl
SET b=5
OUTPUT DELETED.*,INSERTED.* INTO @UpdateOutput
WHERE a=3
SELECT * FROM @UpdateOutput
7.定义参照完整性功能的增强：ANSI标准针对外键约束在修改数据时，定义了四种应答方式：没有操作（No Action）、级联式（Cascade）、设为默认（SET DEFAULT）、设为空（SET NULL）
------------------------------------------
SQL2005支持的两条新语法OUTPUT ... INTO
时间:2009-5-3 16:01:52 来源:www.cnblogs.com 作者:webmaster
1. OUTPUT ... INTO
用于将一条记录从表一移动到表二时非常好用，常见于备份记录的应用
例一：
DELETE [TableUseing] OUTPUT Deleted.* INTO [TableBak]例二：（用于移动时修改某字段的值）
DELETE [TableUseing] OUTPUT Deleted.Field1, Deleted.Field2, GETDATE() INTO [TableBak]
2.表变量
用于存储临时的查询结果
DECLARE @tmp TABLE (ID INT, CITY NVARCHAR(50))INSERT INTO @tmp SELECT MIN(ID), CITY FROM [TableTest] WITH(NOCLOCK) GROUP BY CITY
文章来自学IT网：http://www.xueit.com/html/2009-05/26_1343_00.html
---------------------------------------------
--create by liuchengyuan
--一.row_number(),rank,dense_rank(),ntile()用法
--四个都是排序编号,
--row_number()按顺序编号,如果所排序字段值相同,则产生的行号可能不同
--rank为按排序字段分组,每组序号为各组第一行的当前行号
--dense_rank为按排序字段分组,每组序号为各组的实际排序序号
--ntile(n) 为按排序字段分组,把数据分成n份,每份为 总数据行/n,如果剩余,则按顺序从上到下增加一行,直到增加完为止
--所以每份不一定相等,但最多相差一行.可用于归类
select row_number() over(order by dept_id desc) as row_id,
rank() over(order by dept_id desc) as rk_id,
dense_rank() over(order by dept_id desc) as drk_id,
ntile(5) over(order by dept_id) as ntile_id,
dept_id,user_id
from usermnt where dept_id in('dept121','dept126','dept127','dept179','dept63')
order by dept_id desc;
--如下例子
row_id rk_id drk_id ntile_id dept_id user_id
-------------------- -------------------- -------------------- -------------------- ---------- ----------
1 1 1 5 DEPT179 YAOZY
2 2 2 4 DEPT127 LHT
3 2 2 4 DEPT127 AMYCHAN
4 4 3 3 DEPT126 CY
5 5 4 1 DEPT121 LINA
6 5 4 1 DEPT121 PENGYANG
7 5 4 2 DEPT121 SHIRLEY
8 5 4 2 DEPT121 LUNU
9 5 4 3 DEPT121 DEAN
(9 row(s) affected)
--二,CET with
--递归
with dept_cet(id,lv)
as
(select id,0
from department where top_dept = 'Y'
union all
select a.id ,lv+1
from department as a join
dept_cet as b
on a.superior_dept = b.id)
select * from dept_cet
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/chyliu/archive/2008/05/27/2486901.aspx
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10054891/viewspace-623312/ ，如需转载，请注明出处，否则将追究法律责任。
