[转]SQL Server 2005 Beta 2 Transact-SQL 增强功能
摘要：该白皮书介绍了 Microsoft SQL Server 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能。这些新功能可以改善您的表达能力、查询性能以及错误管理功能。本文重点介绍几个概念新颖且互相联系的增强功能，并且通过实际示例演示这些功能。本文并未讨论所有新增的 Transact-SQL 功能。
本页内容
 |
简介和范围 |
|
改善查询的表达能力和 DRI 支持 |
|
分段 |
|
单父节点环境：雇员组织结构图 |
|
多父节点环境：材料清单 |
|
相关子查询中的表值函数 |
|
性能和错误处理增强功能 |
|
其他影响 Transact-SQL 的 SQL Server 2005 Beta 2 功能 |
|
小结 |
简介和范围
该白皮书介绍了 Microsoft SQL Server 2005 Beta 2 中的 Transact-SQL 的几个新的增强功能。这些新功能可以改善您的表达能力、查询性能以及错误管理功能。本文重点介绍几个概念新颖且互相联系的增强功能，并且通过实际示例演示这些功能。本文并未讨论所有新增的 Transact-SQL 功能。
预备知识：目标读者应该能够熟练使用 Transact-SQL 进行特定查询以及将其作为 Microsoft SQL Server 2000 中应用程序的组件。
改善查询的表达能力和 DRI 支持
本节介绍下列新增的关系功能和增强功能：
| • |
新增的排序函数 |
| • |
新增的基于常见表表达式 (CTE) 的递归查询 |
| • |
新增的 PIVOT 和 APPLY 关系运算符 |
| • |
声明性引用完整性 (DRI) 增强 |
排序函数
SQL Server 2005 引入了四个新的排序函数：ROW_NUMBER、RANK、DENSE_RANK 和 NTILE。这些新函数使您可以有效地分析数据以及向查询的结果行提供排序值。您可能发现这些新函数有用的典型方案包括：将连续整数分配给结果行，以便进行表示、分页、计分和绘制直方图。
Speaker Statistics 方案
下面的 Speaker Statistics 方案将用来讨论和演示不同的函数和它们的子句。大型计算会议包括三个议题：数据库、开发和系统管理。十一位演讲者在会议中发表演讲，并且为他们的讲话获得范围为 1 到 9 的分数。结果被总结并存储在下面的 SpeakerStats 表中：
USE tempdb -- or your own test database
CREATE TABLE SpeakerStats
(
speaker VARCHAR(10) NOT NULL PRIMARY KEY,
track VARCHAR(10) NOT NULL,
score INT NOT NULL,
pctfilledevals INT NOT NULL,
numsessions INT NOT NULL
)
SET NOCOUNT ON
INSERT INTO SpeakerStats VALUES('Dan', 'Sys', 3, 22, 4)
INSERT INTO SpeakerStats VALUES('Ron', 'Dev', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Kathy', 'Sys', 8, 27, 2)
INSERT INTO SpeakerStats VALUES('Suzanne', 'DB', 9, 30, 3)
INSERT INTO SpeakerStats VALUES('Joe', 'Dev', 6, 20, 2)
INSERT INTO SpeakerStats VALUES('Robert', 'Dev', 6, 28, 2)
INSERT INTO SpeakerStats VALUES('Mike', 'DB', 8, 20, 3)
INSERT INTO SpeakerStats VALUES('Michele', 'Sys', 8, 31, 4)
INSERT INTO SpeakerStats VALUES('Jessica', 'Dev', 9, 19, 1)
INSERT INTO SpeakerStats VALUES('Brian', 'Sys', 7, 22, 3)
INSERT INTO SpeakerStats VALUES('Kevin', 'DB', 7, 25, 4)
每个演讲者都在该表中具有一个行，其中含有该演讲者的名字、议题、平均得分、填写评价的与会者相对于参加会议的与会者数量的百分比以及该演讲者发表演讲的次数。本节演示如何使用新的排序函数分析演讲者统计数据以生成有用的信息。
语义
全部四个排序函数都遵循类似的语法模式：
排序函数
() OVER( [PARTITION BY ] ORDER BY )
该函数只能在查询的两个子句中指定 — 在 SELECT 子句或 ORDER BY 子句中。以下各节详细讨论不同的函数。
ROW_NUMBER
ROW_NUMBER 函数使您可以向查询的结果行提供连续的整数值。例如，假设您要返回所有演讲者的 speaker、track 和 score，同时按照 score 降序向结果行分配从 1 开始的连续值。以下查询通过使用 ROW_NUMBER 函数并指定 OVER (ORDER BY score DESC) 生成所需的结果：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, speaker, track, score FROM SpeakerStats ORDER BY score DESC
以下为结果集：
rownum speaker track score ------ ---------- ---------- ----------- 1 Jessica Dev 9 2 Ron Dev 9 3 Suzanne DB 9 4 Kathy Sys 8 5 Michele Sys 8 6 Mike DB 8 7 Kevin DB 7 8 Brian Sys 7 9 Joe Dev 6 10 Robert Dev 6 11 Dan Sys 3
得分最高的演讲者获得行号 1，得分最低的演讲者获得行号 11。ROW_NUMBER 总是按照请求的排序为不同的行生成不同的行号。请注意，如果在 OVER() 选项中指定的 ORDER BY 列表不唯一，则结果是不确定的。这意味着该查询具有一个以上正确的结果；在该查询的不同调用中，可能获得不同的结果。例如，在我们的示例中，有三个不同的演讲者获得相同的最高得分 (9)：Jessica、Ron 和 Suzanne。由于 SQL Server 必须为不同的演讲者分配不同的行号，因此您应当假设分别分配给 Jessica、Ron 和 Suzanne 的值 1、2 和 3 是按任意顺序分配给这些演讲者的。如果值 1、2 和 3 被分别分配给 Ron、Suzanne 和 Jessica，则结果应该同样正确。
如果您指定一个唯一的 ORDER BY 列表，则结果总是确定的。例如，假设在演讲者之间出现得分相同的情况时，您希望使用最高的 pctfilledevals 值来分出先后。如果值仍然相同，则使用最高的 numsessions 值来分出先后。最后，如果值仍然相同，则使用最低词典顺序 speaker 名字来分出先后。由于 ORDER BY 列表 — score、pctfilledevals、numsessions 和 speaker — 是唯一的，因此结果是确定的：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker) AS rownum, speaker, track, score, pctfilledevals, numsessions FROM SpeakerStats ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
以下为结果集：
rownum speaker track score pctfilledevals numsessions ------ ---------- ---------- ----------- -------------- ----------- 1 Ron Dev 9 30 3 2 Suzanne DB 9 30 3 3 Jessica Dev 9 19 1 4 Michele Sys 8 31 4 5 Kathy Sys 8 27 2 6 Mike DB 8 20 3 7 Kevin DB 7 25 4 8 Brian Sys 7 22 3 9 Robert Dev 6 28 2 10 Joe Dev 6 20 2 11 Dan Sys 3 22 4
新的排序函数的重要好处之一是它们的效率。SQL Server 的优化程序只需要扫描数据一次，以便计算值。它完成该工作的方法是：使用在排序列上放置的索引的有序扫描，或者，如果未创建适当的索引，则扫描数据一次并对其进行排序。
另一个好处是语法的简单性。为了让您感受一下通过使用在 SQL Server 的较低版本中采用的基于集的方法来计算排序值是多么困难和低效，请考虑下面的 SQL Server 2000 查询，它返回与上一个查询相同的结果：
SELECT (SELECT COUNT(*) FROM SpeakerStats AS S2 WHERE S2.score > S1.score OR (S2.score = S1.score AND S2.pctfilledevals > S1.pctfilledevals) OR (S2.score = S1.score AND S2.pctfilledevals = S1.pctfilledevals AND S2.numsessions > S1.numsessions) OR (S2.score = S1.score AND S2.pctfilledevals = S1.pctfilledevals AND S2.numsessions = S1.numsessions AND S2.speaker < S1.speaker)) + 1 AS rownum, speaker, track, score, pctfilledevals, numsessions FROM SpeakerStats AS S1 ORDER BY score DESC, pctfilledevals DESC, numsessions DESC, speaker
该查询显然比 SQL Server 2005 查询复杂得多。此外，对于 SpeakerStats 表中的每个基础行，SQL Server 都必须扫描该表的另一个实例中的所有匹配行。对于基础表中的每个行，平均大约需要扫描该表的一半（最少）行。SQL Server 2005 查询的性能恶化是线性的，而 SQL Server 2000 查询的性能恶化是指数性的。即使是在相当小的表中，性能差异也是显著的。例如，请测试下列查询的性能，它们查询 AdventureWorks 数据库中的 SalesOrderHeader 表，以便按照 SalesOrderID 顺序计算销售定单的行数。SalesOrderHeader 表具有 31,465 行。第一个查询使用 SQL Server 2005 ROW_NUMBER 函数，而第二个查询使用 SQL Server 2000 子查询技术：
-- SQL Server 2005 query SELECT SalesOrderID, ROW_NUMBER() OVER(ORDER BY SalesOrderID) AS rownum FROM Sales.SalesOrderHeader -- SQL Server 2000 query SELECT SalesOrderID, (SELECT COUNT(*) FROM Sales.SalesOrderHeader AS S2 WHERE S2.SalesOrderID <= S1.SalesOrderID) AS rownum FROM Sales.SalesOrderHeader AS S1
我在我的膝上型电脑（Compaq Presario X1020U，CPU：Centrino 1.4 GH，RAM：1GB，本地 HD）上运行该测试。SQL Server 2005 查询只需 1 秒即可完成，而 SQL Server 2000 查询大约需要 12 分钟才能完成。
行号的一个典型应用是通过查询结果分页。给定页大小（以行数为单位）和页号，需要返回属于给定页的行。例如，假设您希望按照“score DESC, speaker”顺序从 SpeakerStats 表中返回第二页的行，并且假定页大小为三行。下面的查询首先按照指定的排序计算派生表 D 中的行数，然后只筛选行号为 4 到 6 的行（它们属于第二页）：
SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, speaker, track, score FROM SpeakerStats) AS D WHERE rownum BETWEEN 4 AND 6 ORDER BY score DESC, speaker
以下为结果集：
rownum speaker track score ------ ---------- ---------- ----------- 4 Kathy Sys 8 5 Michele Sys 8 6 Mike DB 8
用更一般的术语表达就是，给定 @pagenum 变量中的页号和 @pagesize 变量中的页大小，以下查询返回属于预期页的行：
DECLARE @pagenum AS INT, @pagesize AS INT SET @pagenum = 2 SET @pagesize = 3 SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, speaker, track, score FROM SpeakerStats) AS D WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize ORDER BY score DESC, speaker
上述方法对于您只对行的一个特定页感兴趣的特定请求而言已经足够了。但是，当用户发出多个请求时，该方法就不能满足需要了，因为该查询的每个调用都需要您对表进行完整扫描，以便计算行号。当用户可能反复请求不同的页时，为了更有效地进行分页，请首先用所有基础表行（包括计算得到的行号）填充一个临时表，并且对包含这些行号的列进行索引：
SELECT ROW_NUMBER() OVER(ORDER BY score DESC, speaker) AS rownum, * INTO #SpeakerStatsRN FROM SpeakerStats CREATE UNIQUE CLUSTERED INDEX idx_uc_rownum ON #SpeakerStatsRN(rownum)
然后，对于所请求的每个页，发出以下查询：
SELECT rownum, speaker, track, score FROM #SpeakerStatsRN WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize ORDER BY score DESC, speaker
只有属于预期页的行才会被扫描。
分段
可以在行组内部独立地计算排序值，而不是为作为一个组的所有表行计算排序值。为此，请使用 PARTITION BY 子句，并且指定一个表达式列表，以标识应该为其独立计算排序值的行组。例如，以下查询按照“score DESC, speaker”顺序单独分配每个 track 内部的行号：
SELECT track, ROW_NUMBER() OVER( PARTITION BY track ORDER BY score DESC, speaker) AS pos, speaker, score FROM SpeakerStats ORDER BY track, score DESC, speaker
以下为结果集：
track pos speaker score ---------- --- ---------- ----------- DB 1 Suzanne 9 DB 2 Mike 8 DB 3 Kevin 7 Dev 1 Jessica 9 Dev 2 Ron 9 Dev 3 Joe 6 Dev 4 Robert 6 Sys 1 Kathy 8 Sys 2 Michele 8 Sys 3 Brian 7 Sys 4 Dan 3
在 PARTITION BY 子句中指定 track 列会使得为具有相同 track 的每个行组单独计算行号。
RANK, DENSE_RANK
RANK 和 DENSE_RANK 函数非常类似于 ROW_NUMBER 函数，因为它们也按照指定的排序提供排序值，而且可以根据需要在行组（分段）内部提供。但是，与 ROW_NUMBER 不同的是，RANK 和 DENSE_RANK 向在排序列中具有相同值的行分配相同的排序。当 ORDER BY 列表不唯一，并且您不希望为在 ORDER BY 列表中具有相同值的行分配不同的排序时，RANK 和 DENSE_RANK 很有用。RANK 和 DENSE_RANK 的用途以及两者之间的差异可以用示例进行最好的解释。以下查询按照 score DESC 顺序计算不同演讲者的行号、排序和紧密排序值：
SELECT speaker, track, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, RANK() OVER(ORDER BY score DESC) AS rnk, DENSE_RANK() OVER(ORDER BY score DESC) AS drnk FROM SpeakerStats ORDER BY score DESC
以下为结果集：
speaker track score rownum rnk drnk ---------- ---------- ----------- ------ --- ---- Jessica Dev 9 1 1 1 Ron Dev 9 2 1 1 Suzanne DB 9 3 1 1 Kathy Sys 8 4 4 2 Michele Sys 8 5 4 2 Mike DB 8 6 4 2 Kevin DB 7 7 7 3 Brian Sys 7 8 7 3 Joe Dev 6 9 9 4 Robert Dev 6 10 9 4 Dan Sys 3 11 11 5
正如前面讨论的那样，score 列不唯一，因此不同的演讲者可能具有相同的得分。行号确实代表下降的 score 顺序，但是具有相同得分的演讲者仍然获得不同的行号。但是请注意，在结果中，所有具有相同得分的演讲者都获得相同的排序和紧密排序值。换句话说，当 ORDER BY 列表不唯一时，ROW_NUMBER 是不确定的，而 RANK 和 DENSE_RANK 总是确定的。排序值和紧密排序值之间的差异在于，排序代表：具有较高得分的行号加 1，而紧密排序代表：具有明显较高得分的行号加 1。从您迄今为止已经了解的内容中，您可以推导出当 ORDER BY 列表唯一时，ROW_NUMBER、RANK 和 DENSE_RANK 产生完全相同的值。
NTILE
NTILE 使您可以按照指定的顺序，将查询的结果行分散到指定数量的组 (tile) 中。每个行组都获得不同的号码：第一组为 1，第二组为 2，等等。您可以在函数名称后面的括号中指定所请求的组号，在 OVER 选项的 ORDER BY 子句中指定所请求的排序。组中的行数被计算为 total_num_rows / num_groups。如果有余数 n，则前面 n 个组获得一个附加行。因此，可能不会所有组都获得相等数量的行，但是组大小最大只可能相差一行。例如，以下查询按照 score 降序将三个组号分配给不同的 speaker 行：
SELECT speaker, track, score, ROW_NUMBER() OVER(ORDER BY score DESC) AS rownum, NTILE(3) OVER(ORDER BY score DESC) AS tile FROM SpeakerStats ORDER BY score DESC
以下为结果集：
speaker track score rownum tile ---------- ---------- ----------- ------ ---- Jessica Dev 9 1 1 Ron Dev 9 2 1 Suzanne DB 9 3 1 Kathy Sys 8 4 1 Michele Sys 8 5 2 Mike DB 8 6 2 Kevin DB 7 7 2 Brian Sys 7 8 2 Joe Dev 6 9 3 Robert Dev 6 10 3 Dan Sys 3 11 3
在 SpeakerStats 表中有 11 位演讲者。将 11 除以 3 得到组大小 3 和余数 2，这意味着前面 2 个组将获得一个附加行（每个组中有 4 行），而第三个组则不会得到附加行（该组中有 3 行）。组号（tile 号）1 被分配给行 1 到 4，组号 2 被分配给行 5 到 8，组号 3 被分配给行 9 到 11。通过该信息可以生成直方图，并且将项目均匀分布到每个梯级。在我们的示例中，第一个梯级表示具有最高得分的演讲者，第二个梯级表示具有中等得分的演讲者，第三个梯级表示具有最低得分的演讲者。可以使用 CASE 表达式为组号提供说明性的有意义的备选含义：
SELECT speaker, track, score, CASE NTILE(3) OVER(ORDER BY score DESC) WHEN 1 THEN 'High' WHEN 2 THEN 'Medium' WHEN 3 THEN 'Low' END AS scorecategory FROM SpeakerStats ORDER BY track, speaker
以下为结果集：
speaker track score scorecategory ---------- ---------- ----------- ------------- Kevin DB 7 Medium Mike DB 8 Medium Suzanne DB 9 High Jessica Dev 9 High Joe Dev 6 Low Robert Dev 6 Low Ron Dev 9 High Brian Sys 7 Medium Dan Sys 3 Low Kathy Sys 8 High Michele Sys 8 Medium
递归查询和常见表表达式
本节探讨递归 CTE 表达式的细节，并且将它们作为常见问题的解决方案加以应用，以大大简化传统的方法。
常见表表达式
常见表表达式 (CTE) 是一个可以由定义语句引用的临时命名的结果集。在它们的简单形式中，您可以将 CTE 视为更类似于非持续性类型视图的派生表的改进版本。在查询的 FROM 子句中引用 CTE 的方式类似于引用派生表和视图的方式。只须定义 CTE 一次，即可在查询中多次引用它。在 CTE 的定义中，可以引用在同一批处理中定义的变量。您甚至可以在 INSERT、UPDATE、DELETE 和 CREATE VIEW 语句中以与使用视图类似的方式使用 CTE。但是，CTE 的真正威力在于它们的递归功能，即 CTE 可以包含对它们自身的引用。在本文中，首先描述简单形式的 CTE，稍后再描述它们的递归形式。本文讨论通过 CTE 进行的 SELECT 查询。
当您希望像引用表一样引用查询结果，但是不希望在数据库中创建持久性视图时，可以使用派生表。但是，派生表具有 CTE 中所不具有的限制：您无法只在查询中定义派生表一次然后多次使用它。相反，您必须在同一查询中定义多个派生表。但是，您可以定义 CTE 一次并在查询中多次使用它，而无须在数据库中持续保存它。
在提供 CTE 的实际示例之前，首先将 CTE 的基本语法与派生表和视图进行比较。以下是视图、派生表和 CTE 内部的查询的一般形式：
视图
CREATE VIEW <view_name>(<column_aliases>) AS <view_query> GO SELECT * FROM <view_name>
派生表
SELECT * FROM (<derived_table_query>) AS <derived_table_alias>(<column_aliases>)
CTE
WITH <cte_alias>(<column_aliases>) AS ( <cte_query> ) SELECT * FROM <cte_alias>
在关键字 WITH 之后，为 CTE 提供一个别名，并且为它的结果列提供一个可选的别名列表；编写 CTE 的主体；然后从外部查询中引用它。
请注意，如果 CTE 的 WITH 子句不是批处理中的第一个语句，则您应当通过在它前面放置一个分号 (;) 来将其与前面的语句分隔开。分号用来避免与 WITH 子句的其他用法（例如，用于表提示）混淆。尽管您可能会发现并非在所有情况下都需要包含分号，但还是建议您始终如一地使用它。
作为一个实际示例，请考虑 AdventureWorks 数据库中的 HumanResources.Employee 和 Purchasing.PurchaseOrderHeader 表。每个雇员都向 ManagerID 列中指定的经理汇报。Employee 表中的每个雇员都可能在 PurchaseOrderHeader 表中具有相关的定单。假设您希望返回每个雇员的定单数量和最后定单日期，并且在同一行中返回经理的类似详细信息。以下示例显示了如何使用视图、派生表和 CTE 实现解决方案：
视图
CREATE VIEW VEmpOrders(EmployeeID, NumOrders, MaxDate) AS SELECT EmployeeID, COUNT(*), MAX(OrderDate) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID GO SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate, E.ManagerID, OM.NumOrders, OM.MaxDate FROM HumanResources.Employee AS E JOIN VEmpOrders AS OE ON E.EmployeeID = OE.EmployeeID LEFT OUTER JOIN VEmpOrders AS OM ON E.ManagerID = OM.EmployeeID
派生表
SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate, E.ManagerID, OM.NumOrders, OM.MaxDate FROM HumanResources.Employee AS E JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID) AS OE(EmployeeID, NumOrders, MaxDate) ON E.EmployeeID = OE.EmployeeID LEFT OUTER JOIN (SELECT EmployeeID, COUNT(*), MAX(OrderDate) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID) AS OM(EmployeeID, NumOrders, MaxDate) ON E.ManagerID = OM.EmployeeID
CTE
WITH EmpOrdersCTE(EmployeeID, NumOrders, MaxDate) AS ( SELECT EmployeeID, COUNT(*), MAX(OrderDate) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID ) SELECT E.EmployeeID, OE.NumOrders, OE.MaxDate, E.ManagerID, OM.NumOrders, OM.MaxDate FROM HumanResources.Employee AS E JOIN EmpOrdersCTE AS OE ON E.EmployeeID = OE.EmployeeID LEFT OUTER JOIN EmpOrdersCTE AS OM ON E.ManagerID = OM.EmployeeID The CTE's definition must be followed by an outer query, which may or may not refer to it. You cannot refer to the CTE later in the batch after other intervening statements.
您可以在同一 WITH 子句中定义多个 CTE，每一个都引用先前定义的 CTE。逗号用来分隔各个 CTE。例如，假设您希望计算雇员定单数量的最小值、最大值以及二者之间的差值：
WITH EmpOrdersCTE(EmployeeID, Cnt) AS ( SELECT EmployeeID, COUNT(*) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID ), MinMaxCTE(MN, MX, Diff) AS ( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt) FROM EmpOrdersCTE ) SELECT * FROM MinMaxCTE
以下为结果集：
MN MX Diff ----------- ----------- ----------- 160 400 240
在 EmpOrdersCTE 中，计算每个雇员的定单数量。在 MinMaxCTE 中，引用 EmpOrdersCTE 以计算雇员定单数量的最小值、最大值以及二者之间的差值。
注 在 CTE 内部，您并非只能引用恰好在它前面定义的 CTE；相反，您可以引用之前定义的所有 CTE。请注意，不允许向前引用：CTE 可以引用在它前面定义的 CTE 和它本身（参阅后文中的递归查询），但是不能引用在它后面定义的 CTE。例如，如果您在同一 WITH 语句中定义了 CTE C1、C2、C3，则 C2 可以引用 C1 和 C2，但是不能引用 C3。
在另一个示例中，以下代码生成一个直方图，以计算位于最小值和最大值之间的四个定单数量范围内的雇员数量。如果这些计算对您似乎很复杂，则请不要花费时间来试图搞懂它们。该示例的目的是使用实际方案来演示如何在同一 WITH 语句中声明多个 CTE（其中每一个都可能引用前面的 CTE）。
WITH EmpOrdersCTE(EmployeeID, Cnt) AS ( SELECT EmployeeID, COUNT(*) FROM Purchasing.PurchaseOrderHeader GROUP BY EmployeeID ), MinMaxCTE(MN, MX, Diff) AS ( SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt) FROM EmpOrdersCTE ), NumsCTE(Num) AS ( SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 ), StepsCTE(Step, Fromval, Toval) AS ( SELECT Num, CAST(MN + ROUND((Num-1)*((Diff+1)/4.), 0) AS INT), CAST(MN + ROUND((Num)*((Diff+1)/4.), 0) - 1 AS INT) FROM MinMaxCTE CROSS JOIN NumsCTE ), HistogramCTE(Step, Fromval, Toval, Samples) AS ( SELECT S.Step, S.Fromval, S.Toval, COUNT(EmployeeID) FROM StepsCTE AS S LEFT OUTER JOIN EmpOrdersCTE AS OE ON OE.Cnt BETWEEN S.Fromval AND S.Toval GROUP BY S.Step, S.Fromval, S.Toval ) SELECT * FROM HistogramCTE
以下为结果集：
Step Fromval Toval Samples ----------- ----------- ----------- ----------- 1 160 219 2 2 220 280 0 3 281 340 0 4 341 400 10
请注意，第二个 CTE (MinMaxCTE) 引用第一个 (EmpOrdersCTE)；第三个 (NumsCTE) 未引用任何 CTE。第四个 (StepsCTE) 引用第二个和第三个 CTE，而第五个 (HistogramCTE) 引用第一个和第四个 CTE。
递归查询
非递归 CTE 改善了您的表达能力。但是对于每一段使用非递归 CTE 的代码，您通常可以通过使用其他 Transact-SQL 结构（例如，派生表）来编写能够获得相同结果的较短的代码。对于递归 CTE，情况是不同的。本节描述递归查询的语义，并且为组织结构图中雇员的层次结构以及材料清单 (BOM) 方案提供了实际实现。
语义
当 CTE 引用它本身时，它被视为递归的。递归的 CTE 是根据至少两个查询（或者，用递归查询的说法，为成员）构建的。一个是非递归查询，也称为锚定成员 (AM)。另一个是递归查询，也称为递归成员 (RM)。查询由 UNION ALL 运算符分隔。以下示例显示了递归 CTE 的简化的一般形式：
WITH RecursiveCTE() AS ( -- Anchor Member: -- SELECT query that does not refer to RecursiveCTE SELECT ... FROM ... UNION ALL -- Recursive Member -- SELECT query that refers to RecursiveCTE SELECT ... FROM JOIN RecursiveCTE ... ) -- Outer Query SELECT ... FROM RecursiveCTE ...
在逻辑上，您可以将实现递归 CTE 的算法视为：
|
1. |
锚定成员被激活。集 R0（R 表示“结果”）被生成。 |
|
2. |
递归成员被激活，在引用 RecursiveCTE 时获得集 Ri（i = 步骤号）作为输入。集 Ri + 1 被生成。 |
|
3. |
步骤 2 的逻辑被反复运行（在每个迭代中递增步骤号），直到返回空集。 |
|
4. |
外部查询执行，在引用 RecursiveCTE 时，获得以前所有步骤的累积 (UNION ALL) 结果。 |
可以在 CTE 中具有两个以上的成员，但是在递归成员和另一个成员（递归或非递归）之间只能有一个 UNION ALL 运算符。其他运算符（例如，UNION）只能在非递归成员之间使用。与支持隐式转换的常规 UNION 和 UNION ALL 运算符不同，递归 CTE 要求所有成员中的列完全匹配，包括具有相同的数据类型、长度和精度。
在递归 CTE 和传统的递归例程（未必特定于 SQL Server）之间存在相似性。递归例程通常包括三个重要元素 — 该例程的第一个调用、递归终止检查以及对同一例程的递归调用。递归 CTE 中的锚定成员对应于传统递归例程中该例程的第一个调用。递归成员对应于该例程的递归调用。终止检查在递归例程中通常是显式的（例如，借助于 IF 语句），但在递归 CTE 中是隐式的 — 当没有从上一个调用中返回任何行时，递归停止。
下列各节介绍递归 CTE 在单父节点和多父节点环境中的实际示例和用法。
单父节点环境：雇员组织结构图
对于单父节点层次结构方案，使用雇员组织结构图。
注 本节中的示例使用一个名为 Employees 的表，该表具有与 AdventureWorks 中的 HumanResources.Employee 表不同的结构。您应当在自己的测试数据库或 tempdb 中运行代码，而不要在 AdventureWorks 中运行代码。
以下代码生成 Employees 表并且用示例数据填充它：
USE tempdb -- or your own test database CREATE TABLE Employees ( empid int NOT NULL, mgrid int NULL, empname varchar(25) NOT NULL, salary money NOT NULL, CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT FK_Employees_mgrid_empid FOREIGN KEY(mgrid) REFERENCES Employees(empid) ) CREATE INDEX idx_nci_mgrid ON Employees(mgrid) SET NOCOUNT ON INSERT INTO Employees VALUES(1 , NULL, 'Nancy' , $10000.00) INSERT INTO Employees VALUES(2 , 1 , 'Andrew' , $5000.00) INSERT INTO Employees VALUES(3 , 1 , 'Janet' , $5000.00) INSERT INTO Employees VALUES(4 , 1 , 'Margaret', $5000.00) INSERT INTO Employees VALUES(5 , 2 , 'Steven' , $2500.00) INSERT INTO Employees VALUES(6 , 2 , 'Michael' , $2500.00) INSERT INTO Employees VALUES(7 , 3 , 'Robert' , $2500.00) INSERT INTO Employees VALUES(8 , 3 , 'Laura' , $2500.00) INSERT INTO Employees VALUES(9 , 3 , 'Ann' , $2500.00) INSERT INTO Employees VALUES(10, 4 , 'Ina' , $2500.00) INSERT INTO Employees VALUES(11, 7 , 'David' , $2000.00) INSERT INTO Employees VALUES(12, 7 , 'Ron' , $2000.00) INSERT INTO Employees VALUES(13, 7 , 'Dan' , $2000.00) INSERT INTO Employees VALUES(14, 11 , 'James' , $1500.00)
每个雇员都向其 ID 存储在 mgrid 列中的经理汇报。在引用 empid 列的 mgrid 列上定义了一个外键，这意味着经理 ID 必须对应于该表中的一个有效雇员 ID 或者为 NULL。老板 Nancy 在 mgrid 列中具有 NULL 值。经理-雇员关系如图 1 所示。
图 1. 雇员组织结构图
下面是一些可能在 Employees 表上运行的常见请求：
| • |
显示有关 Robert (empid=7) 及其所有级别下属的详细信息。 |
| • |
显示有关比 Janet (empid=3) 低两个级别的所有雇员的详细信息。 |
| • |
显示通向 James (empid=14) 的管理链。 |
| • |
显示有多少个雇员直接或间接向每个经理汇报。 |
| • |
以适当的方式显示所有雇员，以便可以容易地查看他们的层次依赖项。 |
递归 CTE 提供了处理上述请求（它们在本质上是递归的）的手段，而无须在数据库中维护有关层次结构的其他信息。
第一个请求可能是最常见的一个请求：返回某个雇员（例如，empid=7 的 Robert）及其所有级别的下属。以下 CTE 提供了对该请求的解决方案：
WITH EmpCTE(empid, empname, mgrid, lvl) AS ( -- Anchor Member (AM) SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 7 UNION ALL -- Recursive Member (RM) SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees AS E JOIN EmpCTE AS M ON E.mgrid = M.empid ) SELECT * FROM EmpCTE
以下为结果集：
empid empname mgrid lvl ----------- ------------------------- ----------- ----------- 7 Robert 3 0 11 David 7 1 12 Ron 7 1 13 Dan 7 1 14 James 11 2
按照先前描述的递归 CTE 逻辑，该 CTE 被按如下方式处理：
锚定成员被激活，并且从 Employees 表中返回 Robert 的行。请注意在 lvl 结果列中返回的常量 0。
|
1. |
递归成员被反复激活，并且借助于 Employees 和 EmpCTE 之间的联接操作返回上一个结果的直接下属。Employees 代表下属，而 EmpCTE（它包含上一个调用的结果）代表经理：
| ||||||
|
2. |
外部查询从 EmpCTE 中返回所有行。 |
请注意，对于每个递归调用，lvl 值反复递增。
使用该级别计数器，您可以限制递归中的迭代次数。例如，以下 CTE 用来返回比 Janet 低两个级别的所有雇员：
WITH EmpCTEJanet(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 3 UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees as E JOIN EmpCTEJanet as M ON E.mgrid = M.empid WHERE lvl < 2 ) SELECT empid, empname FROM EmpCTEJanet WHERE lvl = 2
以下为结果集：
empid empname ----------- ------------------------- 11 David 12 Ron 13 Dan
该代码示例中比上一个代码示例增加的代码以粗体显示。递归成员中的筛选器 WHERE lvl < 2 被用作递归终止检查 — 当 lvl = 2 时，不会返回任何行，因而递归停止。外部查询中的筛选器 WHERE lvl = 2 用来移除上至级别 2 的所有级别。请注意，从逻辑上讲，外部查询中的筛选器 (lvl = 2) 本身就足以只返回所需的行。递归成员中的筛选器 (lvl < 2) 是出于性能原因而添加的 — 为了在返回 Janet 下的两个级别之后立即停止递归。
正如前面提到的那样，CTE 可以引用在同一批处理中定义的本地变量。例如，为了使查询更一般化，您可以使用变量而不是常量作为雇员 ID 和级别：
DECLARE @empid AS INT, @lvl AS INT SET @empid = 3 -- Janet SET @lvl = 2 -- two levels WITH EmpCTE(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = @empid UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees as E JOIN EmpCTE as M ON E.mgrid = M.empid WHERE lvl < @lvl ) SELECT empid, empname FROM EmpCTE WHERE lvl = @lvl
您可以使用提示在已经调用一定数量的递归迭代之后强行终止查询。可以通过在外部查询的结尾添加 OPTION(MAXRECURSION value) 做到这一点，如以下示例所示：
WITH EmpCTE(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 1 UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees as E JOIN EmpCTE as M ON E.mgrid = M.empid ) SELECT * FROM EmpCTE OPTION (MAXRECURSION 2)
以下为结果集：
empid empname mgrid lvl ----------- ------------------------- ----------- ----------- 1 Nancy NULL 0 2 Andrew 1 1 3 Janet 1 1 4 Margaret 1 1 10 Ina 4 2 7 Robert 3 2 8 Laura 3 2 9 Ann 3 2 .Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1 Statement terminated. Maximum recursion 2 has been exhausted before statement completion
可能返回（但是不能保证返回）迄今生成的结果，并且生成错误 530。您可能会想到使用 MAXRECURSION 选项实现以下请求：使用 MAXRECURSION 提示而不是递归成员中的筛选器返回 Janet 下两个级别的雇员：
WITH EmpCTE(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 3 UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees as E JOIN EmpCTE as M ON E.mgrid = M.empid ) SELECT empid, empname FROM EmpCTE WHERE lvl = 2 OPTION (MAXRECURSION 2)
以下为结果集：
empid empname ----------- ------------------------- 11 David 12 Ron 13 Dan .Net SqlClient Data Provider: Msg 530, Level 16, State 1, Line 1 Statement terminated. Maximum recursion 2 has been exhausted before statement completion
但是请记住，除了不能保证返回结果以外，客户端还将获得错误。在有效场合下使用返回错误的代码不是良好的编程做法。建议您使用先前介绍的筛选器，并且如果您愿意，则请使用 MAXRECURSION 提示作为防止出现无限循环的防护措施。
当未指定该提示时，SQL Server 默认为值 100。当您怀疑存在循环递归调用时，可以使用该值作为防护措施。如果您不希望限制递归调用的次数，则请在提示中将 MAXRECURSION 设置为 0。
作为循环关系的示例，假设您的数据中有错误，并且 Nancy 的经理被意外更改为 James（而不是没有经理）：
UPDATE Employees SET mgrid = 14 WHERE empid = 1
以下循环被引入：1->3->7->11->14->1。如果您尝试运行返回 Nancy 及其所有级别的直接和间接下属的代码，则您会获得一个错误，表明默认的最大递归次数 100 在该语句完成之前耗尽：
WITH EmpCTE(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 1 UNION ALL SELECT E.empid, E.empname, E.mgrid, M.lvl+1 FROM Employees AS E JOIN EmpCTE AS M ON E.mgrid = M.empid ) SELECT * FROM EmpCTE Msg 530, Level 16, State 1, Line 1 Statement terminated. Maximum recursion 100 has been exhausted before statement completion
当然，具有能够预防无限递归调用的安全措施是很好的，但是 MAXRECURSION 在隔离循环和解决数据中的错误方面不能提供多少帮助。为了隔离循环，您可以使用相应的 CTE，以便为每个雇员构建通向该雇员的所有雇员 ID 的枚举路径。调用该结果列路径。在递归成员中，使用 CASE 表达式和 LIKE 谓词检查当前雇员 ID 是否已经出现在经理的路径中。如果答案是肯定的，则意味着您找到了循环。如果找到了循环，则在名为 cycle 的结果列中返回 1，否则返回 0。而且，向递归成员中添加一个筛选器，以确保只返回未检测到循环的经理的下属。最后，向外部查询中添加一个筛选器，以便只返回找到循环的雇员 (cycle = 1)：
WITH EmpCTE(empid, path, cycle)
AS
(
SELECT empid,
CAST('.' + CAST(empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
0
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid,
CAST(M.path + CAST(E.empid AS VARCHAR(10)) + '.' AS VARCHAR(900)),
CASE
WHEN M.path LIKE '%.' + CAST(E.empid AS VARCHAR(10)) + '.%' THEN 1
ELSE 0
END
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
WHERE M.cycle = 0
)
SELECT path FROM EmpCTE
WHERE cycle = 1
path
---------------
.1.3.7.11.14.1.
请注意，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 path 值的表达式在两个成员中都被转换为 varbinary(900) 的原因。一旦检测到循环，您就可以通过将 Nancy 的经理重新更改为没有经理来修复数据中的错误：
UPDATE Employees SET mgrid = NULL WHERE empid = 1
迄今为止提供的递归示例具有一个经理锚定成员和一个用于检索下属的递归成员。某些请求则要求执行相反的操作；例如，请求返回 James 的管理路径（James 及其所有级别的经理）。以下代码提供了对该请求的应答：
WITH EmpCTE(empid, empname, mgrid, lvl) AS ( SELECT empid, empname, mgrid, 0 FROM Employees WHERE empid = 14 UNION ALL SELECT M.empid, M.empname, M.mgrid, E.lvl+1 FROM Employees as M JOIN EmpCTE as E ON M.empid = E.mgrid ) SELECT * FROM EmpCTE
以下为结果集：
empid empname mgrid lvl ----------- ------------------------- ----------- ----------- 14 James 11 0 11 David 7 1 7 Robert 3 2 3 Janet 1 3 1 Nancy NULL 4
锚定成员返回 James 的行。递归成员返回先前返回的雇员或经理的单个经理，因为这里使用的是单父节点层次结构并且请求从单个雇员开始。
您还可以使用递归查询来计算聚合，例如，直接或间接向每个经理汇报的下属的数量：
WITH MgrCTE(mgrid, lvl) AS ( SELECT mgrid, 0 FROM Employees WHERE mgrid IS NOT NULL UNION ALL SELECT M.mgrid, lvl + 1 FROM Employees AS M JOIN MgrCTE AS E ON E.mgrid = M.empid WHERE M.mgrid IS NOT NULL ) SELECT mgrid, COUNT(*) AS cnt FROM MgrCTE GROUP BY mgrid
以下为结果集：
mgrid cnt ----------- ----------- 1 13 2 2 3 7 4 1 7 4 11 1
锚定成员返回一个含有每个雇员的经理 ID 的行。经理 ID 列中的 NULL 被排除，因为它不代表特定的经理。递归成员返回先前返回的经理的经理的经理 ID，NULL 再次被排除。最后，CTE 为每个经理包含像它们的直接或间接下属数量一样多的实例。外部查询负责完成按经理 ID 对结果进行分组以及返回实例计数的任务。
作为针对单父节点层次结构的请求的另一个示例，假设您希望返回 Nancy 的按照层次依赖项排序和缩进的下属。以下代码恰好完成该任务，它按照同辈的雇员 ID 对它们进行排序：
WITH EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(empid AS VARBINARY(900))
FROM Employees
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(900))
FROM Employees AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
| | (5) Steven
| | (6) Michael
| (3) Janet
| | (7) Robert
| | | (11) David
| | | | (14) James
| | | (12) Ron
| | | (13) Dan
| | (8) Laura
| | (9) Ann
| (4) Margaret
| | (10) Ina
要按照 empid 值对同辈进行排序，请为每个雇员构建一个名为 sortcol 的二进制字符串。该字符串由通向每个雇员的管理链中串联在一起的雇员 ID 组成（转换为二进制值）。锚定成员是起始点。它用根雇员的 empid 生成一个二进制值。在每个迭代中，递归成员都将被转换为二进制值的当前雇员 ID 追加到经理的 sortcol 中。然后，外部查询按照 sortcol 对结果进行排序。请记住，锚定成员和递归成员中的对应列必须具有相同的数据类型、长度和精度。这就是生成 sortcol 值的表达式被转换为 varbinary(900) 的原因（即使整数的二进制表示需要 4 个字节）：900 个字节覆盖 225 个级别（这似乎不是一个合理的限制）。如果您希望支持更多的级别，则可以增加该长度，但是，请确保在这两个成员中执行该操作；否则，您将获得错误。
层次缩进是通过将字符串（在该示例中为 ' | '）复制与雇员的级别数一样多的次数实现的。为此，需要在括号中追加雇员 ID 本身，并且最后还追加雇员名字。
可以使用类似的技术，按照其他可以转换为较小的定长二进制值的属性（例如，smalldatetime 列中存储的雇员雇用日期）对同辈进行排序。如果您希望按照不可转换为较小的定长二进制值的属性（例如，雇员名字）对同辈进行排序，则可以首先产生按照表示所需排序的经理 ID 分段的整数行号（有关行号的详细信息，请参阅前文中的“排序函数”一节），如下所示：
SELECT empid, empname, mgrid, ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos FROM Employees
并且，请串联被转换为二进制值的雇员位置，而不是串联被转换为二进制值的雇员 ID：
WITH EmpPos(empid, empname, mgrid, pos)
AS
(
SELECT empid, empname, mgrid,
ROW_NUMBER() OVER(PARTITION BY mgrid ORDER BY empname) AS pos
FROM Employees
),
EmpCTE(empid, empname, mgrid, lvl, sortcol)
AS
(
SELECT empid, empname, mgrid, 0,
CAST(pos AS VARBINARY(900))
FROM EmpPos
WHERE empid = 1
UNION ALL
SELECT E.empid, E.empname, E.mgrid, M.lvl+1,
CAST(sortcol + CAST(E.pos AS BINARY(4)) AS VARBINARY(900))
FROM EmpPos AS E
JOIN EmpCTE AS M
ON E.mgrid = M.empid
)
SELECT
REPLICATE(' | ', lvl)
+ '(' + (CAST(empid AS VARCHAR(10))) + ') '
+ empname AS empname
FROM EmpCTE
ORDER BY sortcol
(1) Nancy
| (2) Andrew
| | (6) Michael
| | (5) Steven
| (3) Janet
| | (9) Ann
| | (8) Laura
| | (7) Robert
| | | (13) Dan
| | | (11) David
| | | | (14) James
| | | (12) Ron
| (4) Margaret
| | (10) Ina
要按照其他任何属性或属性组合对同辈进行排序，只须在 ROW_NUMBER 函数的 OVER 选项的 ORDER BY 列表中指定所需的属性，而不是指定 empname。
多父节点环境：材料清单
在上一节中，使用 CTE 处理层次结构，其中，树中的每个节点都只有单个父节点。更复杂的关系情况是这样的图形，其中每个节点都可能具有一个以上的父节点。本节描述材料清单 (BOM) 方案中 CTE 的用法。BOM 是一个非循环有向图形，这意味着每个节点都可以具有一个以上的父节点；节点不能是其本身的直接或间接父节点；两个节点之间的关系不是双重的（例如，A 包含 C，但是 C 不能包含 A）。图 2 显示了 BOM 方案中的项之间的关系。
图 2. 多父节点环境
项 A 包含项 D、B 和 C；项 C 包含 B 和 E；项 B 包含在项 A 和 C 中；等等。以下代码创建了 Items 和 BOM 表，并且用示例数据填充它们：
CREATE TABLE Items
(
itemid VARCHAR(5) NOT NULL PRIMARY KEY,
itemname VARCHAR(25) NOT NULL,
/* other columns, e.g., unit_price, measurement_unit */
)
CREATE TABLE BOM
(
itemid VARCHAR(5) NOT NULL REFERENCES Items,
containsid VARCHAR(5) NOT NULL REFERENCES Items,
qty INT NOT NULL
/* other columns, e.g., quantity */
PRIMARY KEY(itemid, containsid),
CHECK (itemid <> containsid)
)
SET NOCOUNT ON
INSERT INTO Items(itemid, itemname) VALUES('A', 'Item A')
INSERT INTO Items(itemid, itemname) VALUES('B', 'Item B')
INSERT INTO Items(itemid, itemname) VALUES('C', 'Item C')
INSERT INTO Items(itemid, itemname) VALUES('D', 'Item D')
INSERT INTO Items(itemid, itemname) VALUES('E', 'Item E')
INSERT INTO Items(itemid, itemname) VALUES('F', 'Item F')
INSERT INTO Items(itemid, itemname) VALUES('G', 'Item G')
INSERT INTO Items(itemid, itemname) VALUES('H', 'Item H')
INSERT INTO Items(itemid, itemname) VALUES('I', 'Item I')
INSERT INTO Items(itemid, itemname) VALUES('J', 'Item J')
INSERT INTO Items(itemid, itemname) VALUES('K', 'Item K')
INSERT INTO BOM(itemid, containsid, qty) VALUES('E', 'J', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'E', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'C', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'C', 4)
INSERT INTO BOM(itemid, containsid, qty) VALUES('C', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'F', 1)
INSERT INTO BOM(itemid, containsid, qty) VALUES('B', 'G', 3)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'B', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('A', 'D', 2)
INSERT INTO BOM(itemid, containsid, qty) VALUES('H', 'I', 1)
Items 表包含与每个项对应的行。BOM 表包含该图形中节点之间的关系。每个关系都由父项 ID (itemid)、子项 ID (containsid) 以及 itemid (qty) 中 containsid 的数量组成。
BOM 方案中的常见请求是使项“爆炸”：即遍历图形 — 从给定项开始并返回它直接或间接包含的所有项。这听起来可能很熟悉，因为它类似于“雇员组织结构图”一节中介绍的从树中返回子树的操作。但是，在有向图形中，该请求在概念上更为复杂一些，因为可以从多个不同的包含项通过不同的路径到达一个被包含的项。例如，假设您要使项 A 爆炸。请注意，有两个不同的路径从它通向项 B：A->B 和 A->C->B。这意味着项 B 会被到达两次，继而意味着 B 包含的所有项（F 和 G）会被到达两次。幸运的是，通过 CTE 实现这样的请求就像实现从树中获得子树的请求一样简单：
WITH BOMCTE AS ( SELECT * FROM BOM WHERE itemid = 'A' UNION ALL SELECT BOM.* FROM BOM JOIN BOMCTE ON BOM.itemid = BOMCTE.containsid ) SELECT * FROM BOMCTE
以下为结果集：
itemid containsid qty ------ ---------- ----------- A B 2 A C 2 A D 2 C B 2 C E 3 E J 1 B F 1 B G 3 B F 1 B G 3
锚定成员从 BOM 中返回 A 直接包含的所有项。对于 CTE 的上一个迭代返回的每个被包含的项，递归成员都通过将 BOM 与 BOMCTE 联接来返回它包含的项。从逻辑上讲，（未必是输出中的顺序）首先返回 (A, B)、(A, C)、(A, D)，然后返回 (B, F)、(B, G)、(C, B)、(C, E)；最后返回 (B, F)、(B, G)、(E, J)。请注意，BOM 中的大多数请求都不需要您在最后结果中显示某个项一次以上。如果您只是希望显示爆炸中涉及到“哪些”项，则可以使用 DISTINCT 子句消除重复项：
WITH BOMCTE AS ( SELECT * FROM BOM WHERE itemid = 'A' UNION ALL SELECT BOM.* FROM BOM JOIN BOMCTE ON BOM.itemid = BOMCTE.containsid ) SELECT DISTINCT containsid FROM BOMCTE
以下为结果集：
containsid ---------- B C D E F G J
为了帮助您了解部分爆炸的过程，将它的中间结果直观地表示为树，其中所有项都被展开到它们的被包含的项。图 3 显示了通过使部分 A 和 H 爆炸而形成的树以及项数量。
图 3. 部分爆炸
将原始请求向前推进一步，您可能对获得每个项的累积数量而不是获得项本身更感兴趣。例如，A 包含 2 个单位的 C。C 包含 3 个单位的 E。E 包含 1 个单位的 J。A 所需的 J 的单位总数是沿从 A 通向 J 的路径的数量的乘积：2*3*1 = 6。图 4 显示了在聚合项之前构成 A 的每个项的累积数量。
图 4. 部分爆炸 — 计算得到的数量
以下 CTE 计算数量的累积乘积：
WITH BOMCTE(itemid, containsid, qty, cumulativeqty) AS ( SELECT *, qty FROM BOM WHERE itemid = 'A' UNION ALL SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty FROM BOM JOIN BOMCTE ON BOM.itemid = BOMCTE.containsid ) SELECT * FROM BOMCTE
以下为结果集：
itemid containsid qty cumulativeqty ------ ---------- ----------- ------------- A B 2 2 A C 2 2 A D 2 2 C B 2 4 C E 3 6 E J 1 6 B F 1 4 B G 3 12 B F 1 2 B G 3 6
该 CTE 将 cumulativeqty 列添加到上一个 CTE 中。锚定成员将被包含的项的数量作为 cumulativeqty 返回。对于下一个级别的每个被包含的项，递归成员都将它的数量乘以它的包含项的累积数量。请注意，从多个路径到达的项在结果中出现多次，每一次都带有对应于每个路径的累积数量。这样的输出本身不是很有意义，但是它可以帮助您了解通向每个项只出现一次的最终结果的中间步骤。要获得 A 中的每个项的总数量，请让外部查询按照 containsid 对结果进行分组：
WITH BOMCTE(itemid, containsid, qty, cumulativeqty) AS ( SELECT *, qty FROM BOM WHERE itemid = 'A' UNION ALL SELECT BOM.*, BOM.qty * BOMCTE.cumulativeqty FROM BOM JOIN BOMCTE ON BOM.itemid = BOMCTE.containsid ) SELECT containsid AS itemid, SUM(cumulativeqty) AS totalqty FROM BOMCTE GROUP BY containsid
以下为结果集：
itemid totalqty ------ ----------- B 6 C 2 D 2 E 6 F 6 G 18 J 6
PIVOT 和 UNPIVOT
PIVOT 和 UNPIVOT 是可以在查询的 FROM 子句中指定的新的关系运算符。它们对一个输入表值表达式执行某种操作，并且产生一个输出表作为结果。PIVOT 运算符将行旋转为列，并且可能同时执行聚合。它基于给定的枢轴列扩大输入表表达式，并生成一个带有与枢轴列中的每个唯一值相对应的列的输出表。UNPIVOT 运算符执行与 PIVOT 运算符相反的操作；它将列旋转为行。它基于枢轴列收缩输入表表达式。
PIVOT
PIVOT 运算符可用来处理开放架构方案以及生成交叉分析报表。
在开放架构方案中，您需要用事先不知道或因实体类型而异的属性集来维护实体。应用程序的用户动态定义这些属性。您将属性拆分到不同的行中，并且只为每个实体实例存储相关的属性，而不是在表中预定义很多列并存储很多空值。
PIVOT 使您可以为开放架构和其他需要将行旋转为列的方案生成交叉分析报表，并且可能同时计算聚合并且以有用的形式呈现数据。
开放架构方案的一个示例是跟踪可供拍卖的项目的数据库。某些属性与所有拍卖项目有关，例如，项目类型、项目的制造日期以及它的初始价格。只有与所有项目有关的属性被存储在 AuctionItems 表中：
CREATE TABLE AuctionItems ( itemid INT NOT NULL PRIMARY KEY NONCLUSTERED, itemtype NVARCHAR(30) NOT NULL, whenmade INT NOT NULL, initialprice MONEY NOT NULL, /* other columns */ ) CREATE UNIQUE CLUSTERED INDEX idx_uc_itemtype_itemid ON AuctionItems(itemtype, itemid) INSERT INTO AuctionItems VALUES(1, N'Wine', 1822, 3000) INSERT INTO AuctionItems VALUES(2, N'Wine', 1807, 500) INSERT INTO AuctionItems VALUES(3, N'Chair', 1753, 800000) INSERT INTO AuctionItems VALUES(4, N'Ring', -501, 1000000) INSERT INTO AuctionItems VALUES(5, N'Painting', 1873, 8000000) INSERT INTO AuctionItems VALUES(6, N'Painting', 1889, 8000000)
其他属性特定于项目类型，并且不同类型的新项目被不断地添加。这样的属性可以存储在不同的 ItemAttributes 表中，其中每个项属性都存储在不同的行中。每个行都包含项目 ID、属性名称和属性值：
CREATE TABLE ItemAttributes ( itemid INT NOT NULL REFERENCES AuctionItems, attribute NVARCHAR(30) NOT NULL, value SQL_VARIANT NOT NULL, PRIMARY KEY (itemid, attribute) ) INSERT INTO ItemAttributes VALUES(1, N'manufacturer', CAST(N'ABC' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(1, N'type', CAST(N'Pinot Noir' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(1, N'color', CAST(N'Red' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(2, N'manufacturer', CAST(N'XYZ' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(2, N'type', CAST(N'Porto' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(2, N'color', CAST(N'Red' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(3, N'material', CAST(N'Wood' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(3, N'padding', CAST(N'Silk' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(4, N'material', CAST(N'Gold' AS NVARCHAR(15))) INSERT INTO ItemAttributes VALUES(4, N'inscription', CAST(N'One ring ...' AS NVARCHAR(50))) INSERT INTO ItemAttributes VALUES(4, N'size', CAST(10 AS INT)) INSERT INTO ItemAttributes VALUES(5, N'artist', CAST(N'Claude Monet' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(5, N'name', CAST(N'Field of Poppies' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(5, N'type', CAST(N'Oil' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(5, N'height', CAST(19.625 AS NUMERIC(9,3))) INSERT INTO ItemAttributes VALUES(5, N'width', CAST(25.625 AS NUMERIC(9,3))) INSERT INTO ItemAttributes VALUES(6, N'artist', CAST(N'Vincent Van Gogh' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(6, N'name', CAST(N'The Starry Night' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(6, N'type', CAST(N'Oil' AS NVARCHAR(30))) INSERT INTO ItemAttributes VALUES(6, N'height', CAST(28.75 AS NUMERIC(9,3))) INSERT INTO ItemAttributes VALUES(6, N'width', CAST(36.25 AS NUMERIC(9,3)))
请注意，sql_variant 数据类型被用于 value 列，因为不同的属性值可能具有不同的数据类型。例如，size 属性存储整数属性值，而 name 属性存储字符串属性值。
假设您希望呈现 ItemAttributes 表中的数据，该表具有与每个油画项目（项目 5、6）相对应的行以及与每个属性相对应的列。如果没有 PIVOT 运算符，则必须编写如下所示的查询：
SELECT itemid, MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist], MAX(CASE WHEN attribute = 'name' THEN value END) AS [name], MAX(CASE WHEN attribute = 'type' THEN value END) AS [type], MAX(CASE WHEN attribute = 'height' THEN value END) AS [height], MAX(CASE WHEN attribute = 'width' THEN value END) AS [width] FROM ItemAttributes AS ATR WHERE itemid IN(5,6) GROUP BY itemid
以下为结果集：
itemid artist name type height width ------ ---------------- ---------------- ---------- ------ ------ 5 Claude Monet Field of Poppies Oil 19.625 25.625 6 Vincent Van Gogh The Starry Night Oil 28.750 36.250
PIVOT 运算符使您可以维护更简短且更可读的代码以获得相同的结果：
SELECT * FROM ItemAttributes AS ATR PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemid IN(5,6)
像大多数新功能一样，对 PIVOT 运算符的理解来自于试验和使用。PIVOT 语法中的某些元素是显而易见的，并且只需要您弄清楚这些元素与不使用新运算符的查询之间的关系。其他元素则是隐藏的。
您可能会发现下列术语能够帮助您理解 PIVOT 运算符的语义：
table_expression
PIVOT 运算符所作用于的虚拟表（查询中位于 FROM 子句和 PIVOT 运算符之间的部分）：在该示例中为 ItemAttributes AS ATR。
pivot_column
table_expression 中您希望将其值旋转为结果列的列：在该示例中为 attribute。
column_list
pivot_column 中您希望将其呈现为结果列的值列表（在 IN 子句前面的括号中）。它们必须表示为合法的标识符：在该示例中为 [artist]、[name]、[type]、[height]、[width]。
aggregate_function
用于生成结果中的数据或列值的聚合函数：在该示例中为 MAX()。
value_column
table_expression 中的用作 aggregate_function 的参数的列：在该示例中为 value。
group_by_list
隐藏的部分 — table_expression 中除 pivot_column 和 value_column 以外所有用来对结果进行分组的列：在该示例中为 itemid。
select_list
SELECT 子句后面的列列表，可能包括 group_by_list 和 column_list 中的任何列。别名可以用来更改结果列的名称：* 在该示例中，返回 group_by_list 和 column_list 中的所有列。
PIVOT 运算符为 group_by_list 中的每个唯一值返回一个行，就好像您的查询带有 GROUP BY 子句并且您指定了这些列一样。请注意，group_by_list 是隐含的；它没有在查询中的任何位置显式指定。它包含 table_expression 中除 pivot_column 和 value_column 以外的所有列。理解这一点可能是理解您用 PIVOT 运算符编写的查询按照它们本身的方式工作以及在某些情况下可能获得错误的原因的关键。
可能的结果列包括 group_by_list 和 中的值。如果您指定星号 (*)，则查询会返回这两个列表。结果列的数据部分或结果列值是通过将 value_column 用作参数的 aggregate_function 计算的。
下面的用各种颜色突出显示的代码说明了使用 PIVOT 运算符的查询中的不同元素：
SELECT * -- itemid, [artist], [name], [type], [height], [width] FROM ItemAttributes AS ATR PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemid IN(5,6)
以下代码将不同的元素与不使用 PIVOT 运算符的查询相关联：
SELECT itemid, MAX(CASE WHEN attribute = 'artist' THEN value END) AS [artist], MAX(CASE WHEN attribute = 'name' THEN value END) AS [name], MAX(CASE WHEN attribute = 'type' THEN value END) AS [type], MAX(CASE WHEN attribute = 'height' THEN value END) AS [height], MAX(CASE WHEN attribute = 'width' THEN value END) AS [width] FROM ItemAttributes AS ATR WHERE itemid IN(5,6) GROUP BY itemid
请注意，您必须显式指定 中的值。PIVOT 运算符没有提供在静态查询中从 pivot_column 动态得到这些值的选项。您可以使用动态 SQL 自行构建查询字符串以达到该目的。
将上一个 PIVOT 查询向前推进一步，假设您希望为每个拍卖项目返回所有与油画相关的属性。您希望包括那些出现在 AuctionItems 中的属性以及那些出现在 ItemAttributes 中的属性。您可能尝试以下查询，它会返回错误：
SELECT * FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemtype = 'Painting'
以下为错误消息：
.Net SqlClient Data Provider:Msg 8156, Level 16, State 1, Line 1 The column 'itemid' was specified multiple times for 'PVT'.
请记住，PIVOT 作用于 table_expression，它是由该查询中 FROM 子句和 PIVOT 子句之间的部分返回的虚拟表。在该查询中，虚拟表包含 itemid 列的两个实例 — 一个源自 AuctionItems，另一个源自 ItemAttributes。您可能会试探按如下方式修改该查询，但是您仍将获得错误：
SELECT ITM.itemid, itemtype, whenmade, initialprice, [artist], [name], [type], [height], [width] FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT WHERE itemtype = 'Painting'
以下为错误消息：
.Net SqlClient Data Provider: Msg 8156, Level 16, State 1, Line 1 The column 'itemid' was specified multiple times for 'PVT'. .Net SqlClient Data Provider: Msg 107, Level 15, State 1, Line 1 The column prefix 'ITM' does not match with a table name or alias name used in the query.
正如前面提到的那样，PIVOT 运算符作用于由 table_expression 返回的虚拟表，而不是作用于 select_list 中的列。select_list 在 PIVOT 运算符执行它的操作之后计算，并且只能引用 group_by_list 和 column_list。这就是在 select_list 中不再识别 ITM 别名的原因。如果您了解这一点，您就会意识到，应当向 PIVOT 提供一个只包含您希望施加作用的列的 table_expression。这包括分组列（只有 itemid 的一个实例，外加 itemtype、whenmade 和 initialprice）、枢轴列 (attribute) 和值列 (value)。您可以通过使用 CTE 或派生表做到这一点。以下是一个使用 CTE 的示例：
WITH PNT AS ( SELECT ITM.*, ATR.attribute, ATR.value FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid WHERE ITM.itemtype = 'Painting' ) SELECT * FROM PNT PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT
以下为结果集：
itemid itemtype whenmade initialprice artist name type height width ------ -------- -------- ------------ ---------------- ---------------- ---- ------ ----- 5 Painting 1873 8000000.0000 Claude Monet Field of Poppies Oil 19.62 25.62 6 Painting 1889 8000000.0000 Vincent Van Gogh The Starry Night Oil 28.75 36.25
以下是一个使用派生表的示例：
SELECT * FROM (SELECT ITM.*, ATR.attribute, ATR.value FROM AuctionItems AS ITM JOIN ItemAttributes AS ATR ON ITM.itemid = ATR.itemid WHERE ITM.itemtype = 'Painting') AS PNT PIVOT ( MAX(value) FOR attribute IN([artist], [name], [type], [height], [width]) ) AS PVT
当您希望生成交叉分析报表以总结数据时，还可以使用 PIVOT。例如，使用 AdventureWorks 数据库中的 Purchasing.PurchaseOrderHeader 表（假设您希望返回每个雇员使用每个购买方法获得的定单数量，并且用购买方法 ID 作为列的枢轴）。请记住，您只应当向 PIVOT 运算符提供相关数据。您可以使用派生表并编写以下查询：
SELECT EmployeeID, [1] AS SM1, [2] AS SM2, [3] AS SM3, [4] AS SM4, [5] AS SM5 FROM (SELECT PurchaseOrderID, EmployeeID, ShipMethodID FROM Purchasing.PurchaseOrderHeader) ORD PIVOT ( COUNT(PurchaseOrderID) FOR ShipMethodID IN([1], [2], [3], [4], [5]) ) AS PVT
以下为结果集：
EmployeeID SM1 SM2 SM3 SM4 SM5 ----------- ----------- ----------- ----------- ----------- ----------- 164 56 62 12 89 141 198 24 27 6 45 58 223 56 67 17 98 162 231 50 67 12 81 150 233 55 62 12 106 125 238 53 58 13 102 134 241 50 59 13 108 130 244 55 47 17 93 148 261 58 54 11 120 117 264 50 58 15 86 151 266 58 68 14 116 144 274 24 26 6 41 63
COUNT(PurchaseOrderID) 函数为列表中的每个托运方法统计行数。请注意，PIVOT 不允许使用 COUNT(*)。列别名用来向结果列提供更具描述性的名称。当您具有较少的托运方法并且它们的 ID 事先已知时，使用 PIVOT 在不同的列中显示每个托运方法的定单计数是合理的。
还可以用从表达式中得到的值为枢轴。例如，假设您希望返回每个定单年中每个雇员的运费总值，并且用年份作为列的枢轴。定单年是从 OrderDate 列中得到的：
SELECT EmployeeID, [2001] AS Y2001, [2002] AS Y2002, [2003] AS Y2003, [2004] AS Y2004 FROM (SELECT EmployeeID, YEAR(OrderDate) AS OrderYear, Freight FROM Purchasing.PurchaseOrderHeader) AS ORD PIVOT ( SUM(Freight) FOR OrderYear IN([2001], [2002], [2003], [2004]) ) AS PVT
以下为结果集：
EmployeeID Y2001 Y2002 Y2003 Y2004 ----------- ----------- ----------- ----------- ------------ 164 509.9325 14032.0215 34605.3459 105087.7428 198 NULL 5344.4771 14963.0595 45020.9178 223 365.7019 12496.0776 37489.2896 117599.4156 231 6.8025 9603.0502 37604.3258 75435.8619 233 1467.1388 9590.7355 32988.0643 98603.745 238 17.3345 9745.1001 37836.583 100106.3678 241 221.1825 6865.7299 35559.3883 114430.983 244 5.026 5689.4571 35449.316 74690.3755 261 NULL 10483.27 32854.9343 73992.8431 264 NULL 10337.3207 37170.1957 82406.4474 266 4.2769 9588.8228 38533.9582 115291.2472 274 NULL 1877.2665 13708.9336 41011.3821
交叉分析报表在数据仓库方案中很常见。请考虑下面的 OrdersFact 表（您用 AdventureWorks 中的销售定单和销售定单详细信息数据填充该表）：
CREATE TABLE OrdersFact ( OrderID INT NOT NULL, ProductID INT NOT NULL, CustomerID NCHAR(5) NOT NULL, OrderYear INT NOT NULL, OrderMonth INT NOT NULL, OrderDay INT NOT NULL, Quantity INT NOT NULL, PRIMARY KEY(OrderID, ProductID) ) INSERT INTO OrdersFact SELECT O.SalesOrderID, OD.ProductID, O.CustomerID, YEAR(O.OrderDate) AS OrderYear, MONTH(O.OrderDate) AS OrderMonth, DAY(O.OrderDate) AS OrderDay, OD.OrderQty FROM Sales.SalesOrderHeader AS O JOIN Sales.SalesOrderDetail AS OD ON O.SalesOrderID = OD.SalesOrderID
要获得每个年份和月份的总数量，并且在行中返回年份，在列中返回月份，则请使用以下查询：
SELECT * FROM (SELECT OrderYear, OrderMonth, Quantity FROM OrdersFact) AS ORD PIVOT ( SUM(Quantity) FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT
以下为结果集：
OrderYear 1 2 3 4 5 6 7 8 9 10 11 12 ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 2001 NULL NULL NULL NULL NULL NULL 966 2209 1658 1403 3132 2480 2002 1040 2303 1841 1467 3179 2418 7755 11325 9066 5584 8268 6672 2003 3532 5431 4132 5694 8278 6444 11288 18986 18681 11607 14771 15855 2004 9227 10999 11314 12239 15656 15805 2209 NULL NULL NULL NULL NULL
对于年份和月份之间不存在的交点，PIVOT 返回空值。如果某个年份出现在输入表表达式（派生表 ORD）中，则它会出现在结果中，而不管它是否与任何指定的月份存在交点。这意味着，如果您未指定所有现有月份，则可能获得在所有列中都含有 NULL 的行。但是，结果中的空值未必代表不存在的交点。它们可能产生自数量列中的基础空值，除非该列不允许使用空值。如果您希望重写 NULL 并且改而考虑另一个值（例如 0），则可以通过在选择列表中使用 ISNULL() 函数做到这一点：
SELECT OrderYear, ISNULL([1], 0) AS M01, ISNULL([2], 0) AS M02, ISNULL([3], 0) AS M03, ISNULL([4], 0) AS M04, ISNULL([5], 0) AS M05, ISNULL([6], 0) AS M06, ISNULL([7], 0) AS M07, ISNULL([8], 0) AS M08, ISNULL([9], 0) AS M09, ISNULL([10], 0) AS M10, ISNULL([11], 0) AS M11, ISNULL([12], 0) AS M12 FROM (SELECT OrderYear, OrderMonth, Quantity FROM OrdersFact) AS ORD PIVOT ( SUM(Quantity) FOR OrderMonth IN([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) ) AS PVT
以下为结果集：
OrderYear 1 2 3 4 5 6 7 8 9 10 11 12 ---------- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- ----- 2001 0 0 0 0 0 0 966 2209 1658 1403 3132 2480 2002 1040 2303 1841 1467 3179 2418 7755 11325 9066 5584 8268 6672 2003 3532 5431 4132 5694 8278 6444 11288 18986 18681 11607 14771 15855 2004 9227 10999 11314 12239 15656 15805 2209 0 0 0 0 0
在派生表中使用 ISNULL(Quantity, 0) 时，只会处理 Quantity 列中的基础空值（如果该列存在），而不会处理 PIVOT 为不存在的交点生成的空值。
假设您希望针对 2003 年和 2004 年的第一个季度中的年份值和月份值组合返回范围 1 到 9 中的每个客户 ID 的总数量。要在行中获得年份值和月份值，在列中获得客户 ID，请使用以下查询：
SELECT * FROM (SELECT CustomerID, OrderYear, OrderMonth, Quantity FROM OrdersFact WHERE CustomerID BETWEEN 1 AND 9 AND OrderYear IN(2003, 2004) AND OrderMonth IN(1, 2, 3)) AS ORD PIVOT ( SUM(Quantity) FOR CustomerID IN([1],[2],[3],[4],[5],[6],[7],[8],[9]) ) AS PVT
以下为结果集：
OrderYear OrderMonth 1 2 3 4 5 6 7 8 9 ----------- ----------- ---- ---- ---- ---- ---- ---- ---- ---- ---- 2003 1 NULL NULL NULL 105 NULL NULL 8 NULL NULL 2004 1 NULL NULL NULL 80 NULL NULL NULL NULL NULL 2003 2 NULL 5 NULL NULL NULL NULL NULL NULL 15 2004 2 NULL 10 NULL NULL NULL NULL NULL 6 3 2003 3 NULL NULL 105 NULL 15 NULL NULL NULL NULL 2004 3 NULL NULL 103 NULL 25 4 NULL NULL NULL
在该示例中，隐含的 group-by 列表为 OrderYear 和 OrderMonth，因为 CustomerID 和 Quantity 分别被用作枢轴列和值列。
但是，如果您希望年份值和月份值的组合显示为列，则必须首先自己串联它们，然后再将它们传递给 PIVOT 运算符，因为只能有一个枢轴列：
SELECT * FROM (SELECT CustomerID, OrderYear*100+OrderMonth AS YM, Quantity FROM OrdersFact WHERE CustomerID BETWEEN 1 AND 9 AND OrderYear IN(2003, 2004) AND OrderMonth IN(1, 2, 3)) AS ORD PIVOT ( SUM(Quantity) FOR YM IN([200301],[200302],[200303],[200401],[200402],[200403]) ) AS PVT
以下为结果集：
CustomerID 200301 200302 200303 200401 200402 200403 ---------- ------ ------ ------ ------ ------ ------ 2 NULL 5 NULL NULL 10 NULL 3 NULL NULL 105 NULL NULL 103 6 NULL NULL NULL NULL NULL 4 4 105 NULL NULL 80 NULL NULL 8 NULL NULL NULL NULL 6 NULL 5 NULL NULL 15 NULL NULL 25 7 8 NULL NULL NULL NULL NULL 9 NULL 15 NULL NULL 3 NULL
UNPIVOT
UNPIVOT 运算符使您可以标准化预先旋转的数据。UNPIVOT 运算符的语法和元素与 PIVOT 运算符类似。
例如，请考虑上一节中的 AuctionItems 表：
itemid itemtype whenmade initialprice ----------- ------------------------ ----------- -------------- 1 Wine 1822 3000.0000 2 Wine 1807 500.0000 3 Chair 1753 800000.0000 4 Ring -501 1000000.0000 5 Painting 1873 8000000.0000 6 Painting 1889 8000000.0000
假设您希望每个属性出现在不同的行中（类似于在 ItemAttributes 表中保存属性的方式）：
itemid attribute value ----------- --------------- ------- 1 itemtype Wine 1 whenmade 1822 1 initialprice 3000.00 2 itemtype Wine 2 whenmade 1807 2 initialprice 500.00 3 itemtype Chair 3 whenmade 1753 3 initialprice 800000.00 4 itemtype Ring 4 whenmade -501 4 initialprice 1000000.00 5 itemtype Painting 5 whenmade 1873 5 initialprice 8000000.00 6 itemtype Painting 6 whenmade 1889 6 initialprice 8000000.00
在 UNPIVOT 查询中，您希望将列 itemtype、whenmade 和 initialprice 旋转到行。每个行都应当具有项 ID、属性和值。您必须提供的新的列名称为 attribute 和 value。它们对应于 PIVOT 运算符中的 pivot_column 和 value_column。attribute 列应当获得您希望旋转的实际列名称（itemtype、whenmade 和 initialprice）作为值。value 列应当将来自三个不同源列中的值放到一个目标列中。为了有助于进行说明，首先介绍一个无效的 UNPIVOT 查询版本，然后介绍一个应用了某些限制的有效版本：
SELECT itemid, attribute, value FROM AuctionItems UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV
作为 PIVOT 运算符的参数，您为后面跟 FOR 子句的 value_column（在该示例中为 value）提供一个名称。在 FOR 子句后面，为 pivot_column（在该示例中为 attribute）提供一个名称，然后提供一个 IN 子句，其中带有您希望获得以作为 pivot_column 中的值的源列名称的列表。在 PIVOT 运算符中，该列列表被引用为<column_list> 。该查询生成以下错误：
.Net SqlClient Data Provider:Msg 8167, Level 16, State 1, Line 1 Type of column 'whenmade' conflicts with the type of other columns specified in the UNPIVOT list.
目标 value 列包含源自多个不同源列（那些出现在<column_list> 中的列）的值。因为所有列值的目标是单个列，所以 UNPIVOT 要求<column_list> 中的所有列都具有相同的数据类型、长度和精度。要满足该限制，可以向 UNPIVOT 运算符提供一个表表达式，以便将这三个列转换为相同的数据类型。sql_variant 数据类型是一个良好的候选类型，因为您可以将不同的源列转换为相同的数据类型，并且仍然保留它们的原始数据类型。应用该限制，您可以按如下方式修改上一个查询并获得所需的结果：
SELECT itemid, attribute, value FROM (SELECT itemid, CAST(itemtype AS SQL_VARIANT) AS itemtype, CAST(whenmade AS SQL_VARIANT) AS whenmade, CAST(initialprice AS SQL_VARIANT) AS initialprice FROM AuctionItems) AS ITM UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV
结果 attribute 列的数据类型为 sysname。这是 SQL Server 用于存储对象名称的数据类型。
请注意，UNPIVOT 运算符从结果中消除了 value 列中的空值；因此，不能将其视为 PIVOT 运算符的严格逆操作。
在将 AuctionItems 中的列旋转为行之后，您现在可以将 UNPIVOT 操作的结果与 ItemAttributes 中的行合并，以提供统一的结果：
SELECT itemid, attribute, value FROM (SELECT itemid, CAST(itemtype AS SQL_VARIANT) AS itemtype, CAST(whenmade AS SQL_VARIANT) AS whenmade, CAST(initialprice AS SQL_VARIANT) AS initialprice FROM AuctionItems) AS ITM UNPIVOT ( value FOR attribute IN([itemtype], [whenmade], [initialprice]) ) AS UPV UNION ALL SELECT * FROM ItemAttributes ORDER BY itemid, attribute
以下为结果集：
itemid attribute value ----------- --------------- ------------- 1 color Red 1 initialprice 3000.00 1 itemtype Wine 1 manufacturer ABC 1 type Pinot Noir 1 whenmade 1822 2 color Red 2 initialprice 500.00 2 itemtype Wine 2 manufacturer XYZ 2 type Porto 2 whenmade 1807 3 initialprice 800000.00 3 itemtype Chair 3 material Wood 3 padding Silk 3 whenmade 1753 4 initialprice 1000000.00 4 inscription One ring 4 itemtype Ring 4 material Gold 4 size 10 4 whenmade -501 5 height 19.625 5 initialprice 8000000.00 5 itemtype Painting 5 name Field of Poppies 5 artist Claude Monet 5 type Oil 5 whenmade 1873 5 width 25.625 6 height 28.750 6 initialprice 8000000.00 6 itemtype Painting 6 name The Starry Night 6 artist Vincent Van Gogh 6 type Oil 6 whenmade 1889 6 width 36.250
APPLY
APPLY 关系运算符使您可以针对外部表表达式的每个行调用指定的表值函数一次。您可以在查询的 FROM 子句中指定 APPLY，其方式与使用 JOIN 关系运算符类似。APPLY 具有两种形式：CROSS APPLY 和 OUTER APPLY。通过 APPLY 运算符，SQL Server 2005 Beta 2 使您可以在相关子查询中引用表值函数。
CROSS APPLY
CROSS APPLY 为外部表表达式中的每个行调用表值函数。您可以引用外部表中的列作为该表值函数的参数。CROSS APPLY 从该表值函数的单个调用所返回的所有结果中返回统一的结果集。如果该表值函数对于给定的外部行返回空集，则不会在结果中返回该外部行。例如，以下表值函数接受两个整数作为参数，并且返回带有一个行的表 — 该表用最小值和最大值作为列：
CREATE FUNCTION dbo.fn_scalar_min_max(@p1 AS INT, @p2 AS INT) RETURNS TABLE AS RETURN SELECT CASE WHEN @p1 < @p2 THEN @p1 WHEN @p2 < @p1 THEN @p2 ELSE COALESCE(@p1, @p2) END AS mn, CASE WHEN @p1 > @p2 THEN @p1 WHEN @p2 > @p1 THEN @p2 ELSE COALESCE(@p1, @p2) END AS mx GO SELECT * FROM fn_scalar_min_max(10, 20)
以下为结果集：
mn mx ----------- ----------- 10 20
给定下面的 T1 表：
CREATE TABLE T1 ( col1 INT NULL, col2 INT NULL ) INSERT INTO T1 VALUES(10, 20) INSERT INTO T1 VALUES(20, 10) INSERT INTO T1 VALUES(NULL, 30) INSERT INTO T1 VALUES(40, NULL) INSERT INTO T1 VALUES(50, 50)
您希望为 T1 中的每个行调用 fn_scalar_min_max。您可以按如下方式编写 CROSS APPLY 查询：
SELECT * FROM T1 CROSS APPLY fn_scalar_min_max(col1, col2) AS M
以下为结果集：
col1 col2 mn mx ----------- ----------- ----------- ----------- 10 20 10 20 20 10 10 20 NULL 30 30 30 40 NULL 40 40 50 50 50 50
如果该表值函数为特定的外部行返回多个行，则该外部行被多次返回。考虑在本文前面的递归查询和常见表表达式一节中使用的 Employees 表（“雇员组织结构图”方案）。在同一数据库中，您还创建了以下 Departments 表：
CREATE TABLE Departments ( deptid INT NOT NULL PRIMARY KEY, deptname VARCHAR(25) NOT NULL, deptmgrid INT NULL REFERENCES Employees ) SET NOCOUNT ON INSERT INTO Departments VALUES(1, 'HR', 2) INSERT INTO Departments VALUES(2, 'Marketing', 7) INSERT INTO Departments VALUES(3, 'Finance', 8) INSERT INTO Departments VALUES(4, 'R&D', 9) INSERT INTO Departments VALUES(5, 'Training', 4) INSERT INTO Departments VALUES(6, 'Gardening', NULL)
大多数部门都具有一个与 Employees 表中的某个雇员相对应的经理 ID，但是像 Gardening 部门一样，有些部门可能没有经理。请注意，Employees 表中的经理必然管理某个部门。以下表值函数接受雇员 ID 作为参数，并且返回该雇员及其所有级别的所有下属：
CREATE FUNCTION dbo.fn_getsubtree(@empid AS INT) RETURNS @TREE TABLE ( empid INT NOT NULL, empname VARCHAR(25) NOT NULL, mgrid INT NULL, lvl INT NOT NULL ) AS BEGIN WITH Employees_Subtree(empid, empname, mgrid, lvl) AS ( -- Anchor Member (AM) SELECT empid, empname, mgrid, 0 FROM employees WHERE empid = @empid UNION all -- Recursive Member (RM) SELECT e.empid, e.empname, e.mgrid, es.lvl+1 FROM employees AS e JOIN employees_subtree AS es ON e.mgrid = es.empid ) INSERT INTO @TREE SELECT * FROM Employees_Subtree RETURN END GO
要为每个部门的经理返回所有级别的所有下属，请使用以下查询：
SELECT * FROM Departments AS D CROSS APPLY fn_getsubtree(D.deptmgrid) AS ST
以下为结果集：
deptid deptname deptmgrid empid empname mgrid lvl ----------- ---------- ----------- ----------- ---------- ----------- --- 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1
这里需要注意两个事情。第一，Departments 中的每个行都被复制与从 fn_getsubtree 中为该部门的经理返回的行数一样多的次数。第二，Gardening 部门不会出现在结果中，因为 fn_getsubtree 为其返回空集。
CROSS APPLY 运算符的另一个实际运用可以满足以下常见请求：为每个组返回 n 行。例如，以下函数返回给定客户的请求数量的最新定单：
USE AdventureWorks GO CREATE FUNCTION fn_getnorders(@custid AS INT, @n AS INT) RETURNS TABLE AS RETURN SELECT TOP(@n) * FROM Sales.SalesOrderHeader WHERE CustomerID = @custid ORDER BY OrderDate DESC GO
使用 CROSS APPLY 运算符，可以通过下面的简单查询获得每个客户的两个最新定单：
SELECT O.* FROM Sales.Customer AS C CROSS APPLY fn_getnorders(C.CustomerID, 2) AS O
有关 TOP 增强功能的详细信息，请参阅下文中的“TOP 增强功能”。
OUTER APPLY
OUTER APPLY 非常类似于 CROSS APPLY，但是它还从表值函数为其返回空集的外部表中返回行。空值作为与表值函数的列相对应的列值返回。例如，修改针对上一节中的 Departments 表的查询以使用 OUTER APPLY 而不是 CROSS APPLY，并且注意输出中的最后一行：
SELECT * FROM Departments AS D OUTER APPLY fn_getsubtree(D.deptmgrid) AS ST
以下为结果集：
deptid deptname deptmgrid empid empname mgrid lvl ----------- ---------- ----------- ----------- ---------- ----------- --- 1 HR 2 2 Andrew 1 0 1 HR 2 5 Steven 2 1 1 HR 2 6 Michael 2 1 2 Marketing 7 7 Robert 3 0 2 Marketing 7 11 David 7 1 2 Marketing 7 12 Ron 7 1 2 Marketing 7 13 Dan 7 1 2 Marketing 7 14 James 11 2 3 Finance 8 8 Laura 3 0 4 R&D 9 9 Ann 3 0 5 Training 4 4 Margaret 1 0 5 Training 4 10 Ina 4 1 6 Gardening NULL NULL NULL NULL NULL
相关子查询中的表值函数
在 SQL Server 2000 中，不能在相关子查询内部引用表值函数。与提供 APPLY 关系运算符一道，该限制在 SQL Server 2005 Beta 2 中被移除。现在，在子查询内部，可以向表值函数提供外部查询中的列作为参数。例如，如果您希望只返回那些经理至少具有三名雇员的部门，则可以编写以下查询：
SELECT * FROM Departments AS D WHERE (SELECT COUNT(*) FROM fn_getsubtree(D.deptmgrid)) >= 3 deptid deptname deptmgrid ----------- ------------------------- ----------- 1 HR 2 2 Marketing 7
对新的 DRI 操作的支持：
SET DEFAULT 和 SET NULL
ANSI SQL 定义了四个可能的引用操作，以支持 FOREIGN KEY 约束。您可以指定这些操作，以表明您希望系统如何响应针对由外键引用的表的 DELETE 或 UPDATE 操作。SQL Server 2000 支持这些操作中的两个：NO ACTION 和 CASCADE。SQL Server 2005 Beta 2 添加了对 SET DEFAULT 和 SET NULL 引用操作的支持。
SET DEFAULT 和 SET NULL 引用操作扩展了声明性引用完整性 (DRI) 功能。您可以在外键声明中将这些选项与 ON UPDATE 和 ON DELETE 子句结合使用。SET DEFAULT 意味着，当您在被引用的表中删除行 (ON DELETE) 或更新被引用的键 (ON UPDATE) 时，SQL Server 会将引用表中的相关行的引用列值设置为该列的默认值。类似地，如果您使用 SET NULL 选项，则 SQL Server 可以通过将值设置为 NULL 进行反应（前提是引用列允许使用空值）。
例如，以下 Customers 表具有三个真实客户和一个虚拟客户：
CREATE TABLE Customers
(
customerid CHAR(5) NOT NULL,
/* other columns */
CONSTRAINT PK_Customers PRIMARY KEY(customerid)
)
INSERT INTO Customers VALUES('DUMMY')
INSERT INTO Customers VALUES('FRIDA')
INSERT INTO Customers VALUES('GNDLF')
INSERT INTO Customers VALUES('BILLY')
Orders 表跟踪定单。不一定非要将定单分配给真实客户。如果您输入一个定单并且未指定客户 ID，则默认情况下会将 DUMMY 客户 ID 分配给该定单。在从 Customers 表中进行删除时，您希望 SQL Server 在 Orders 中的相关行的 customerid 列中设置 NULL。customerid 列中含有 NULL 的定单成为“孤儿”，也就是说，它们不属于任何客户。假设您还希望允许对 Customers 中的 customerid 列进行更新。您可能希望将对 Orders 中的相关行进行的更新级联，但是假设公司的业务规则另行规定：应当将属于 ID 被更改的客户的定单与默认客户 (DUMMY) 相关联。在对 Customers 中的 customerid 列进行更新时，您希望 SQL Server 将默认值 'DUMMY' 设置为 Orders 中的相关客户 ID (customerid)。您用外键按如下方式创建 Orders 表，并且用一些定单填充它：
CREATE TABLE Orders
(
orderid INT NOT NULL,
customerid CHAR(5) NULL DEFAULT('DUMMY'),
orderdate DATETIME NOT NULL,
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers
FOREIGN KEY(customerid)
REFERENCES Customers(customerid)
ON DELETE SET NULL
ON UPDATE SET DEFAULT
)
INSERT INTO Orders VALUES(10001, 'FRIDA', '20040101')
INSERT INTO Orders VALUES(10002, 'FRIDA', '20040102')
INSERT INTO Orders VALUES(10003, 'BILLY', '20040101')
INSERT INTO Orders VALUES(10004, 'BILLY', '20040103')
INSERT INTO Orders VALUES(10005, 'GNDLF', '20040104')
INSERT INTO Orders VALUES(10006, 'GNDLF', '20040105')
要测试 SET NULL 和 SET DEFAULT 选项，请发出下列 DELETE 和 UPDATE 语句：
DELETE FROM Customers WHERE customerid = 'FRIDA' UPDATE Customers SET customerid = 'DOLLY' WHERE customerid = 'BILLY'
结果，FRIDA 的定单被分配 customerid 列中的空值，而 BILLY 的定单被分配 DUMMY：
orderid customerid orderdate ----------- ---------- ---------------------- 10001 NULL 1/1/2004 12:00:00 AM 10002 NULL 1/2/2004 12:00:00 AM 10003 DUMMY 1/1/2004 12:00:00 AM 10004 DUMMY 1/3/2004 12:00:00 AM 10005 GNDLF 1/4/2004 12:00:00 AM 10006 GNDLF 1/5/2004 12:00:00 AM
请注意，如果您使用 SET DEFAULT 选项，引用列具有非空默认值且该值在被引用的表中不具有相应值，则当您发出触发操作时，将获得错误。例如，如果您从 Customers 中删除 DUMMY 客户，然后将 GNDLF 的 customerid 更新为 GLDRL，则会获得错误。UPDATE 触发一个 SET DEFAULT 操作，该操作试图向 GNDLF 的原始定单分配在 Customers 中不具有相应行的 DUMMY 客户 ID：
DELETE FROM Customers WHERE customerid = 'DUMMY' UPDATE Customers SET customerid = 'GLDRL' WHERE customerid = 'GNDLF' .Net SqlClient Data Provider: Msg 547, Level 16, State 0, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Orders_Customers'. The conflict occurred in database 'tempdb', table 'Customers', column 'customerid'. The statement has been terminated.
通过查看 sys.foreign_keys，您可以找到有关外键的详细信息，包括它们的已定义的引用操作。
性能和错误处理增强功能
本节讨论用来解决以前版本的 SQL Server 中的性能问题的增强功能，提高您的数据加载能力，并且显著改善您的错误管理能力。这些增强功能包括 BULK 行集提供程序和 TRY...CATCH 错误处理结构。
BULK 行集提供程序
BULK 是 OPENROWSET 函数中指定的新的行集提供程序，它使您可以访问关系格式的文件数据。为了从文件中检索数据，您可以指定 BULK 选项、文件名以及用 bcp.exe 创建或手动创建的格式文件。您可以在从 OPENROWSET 中返回的表的别名后面的括号中，指定结果列的名称。
以下为您可以用 OPENROWSET 指定的所有选项的新语法：
OPENROWSET
( { 'provider_name'
, { 'datasource' ; 'user_id' ; 'password' | 'provider_string' }
, { [ catalog. ] [ schema. ] object | 'query' }
| BULK 'data_filename',
{FORMATFILE = 'format_file_path' [, ] |
SINGLE_BLOB | SINGLE_CLOB | SINGLE_NCLOB}
}
)
::=
[ , CODEPAGE = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
[ , FIRSTROW = first_row ]
[ , LASTROW = last_row ]
[ , ROWS_PER_BATCH = 'rows_per_batch']
[ , MAXERRORS = 'max_errors']
[ , ERRORFILE ='file_name']
}
)
例如，以下查询从文本文件“c:\temp\textfile1.txt”中返回三个列，并且向结果列提供了列别名 col1、col2 和 col3：
SELECT col1, col2, col3 FROM OPENROWSET(BULK 'c:\temp\textfile1.txt', FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
请注意，当您使用 BULK 选项时，也必须指定格式文件，除非您使用我稍后将描述的 SINGLE_BLOB、SINGLE_CLOB 或 SINGLE_NCLOB 选项。因此，无须指定数据文件类型、字段终止符或行终止符。您可以根据需要与 FORMATFILE 一起指定的其他选项包括：CODEPAGE、FIRSTROW、LASTROW、ROW_PER_BATCH、MAXERRORS 和 ERRORFILE。大多数选项可以通过 SQL Server 2000 中的 BULK INSERT 命令使用。ERRORFILE 选项在概念上是新的。该文件包含零个或更多个具有来自输入数据文件的格式化错误的行（即，这些行无法转换为 OLEDB 行集）。这些行从数据文件中“按原样”复制到该错误文件中。在修复该错误之后，数据就会立即具有预期的格式，因此可以使用相同的命令容易地重新加载它。错误文件是在命令执行开始时创建的。如果该文件已经存在，则会引发错误。通过观察该文件中的行，可以容易地识别失败的行，但是没有办法知道失败的原因。为了解决该问题，自动创建一个扩展名为 .ERROR.txt 的控制文件。该文件引用 ERRORFILE 中的每个行并且提供错误诊断。
您可以使用 BULK 行集提供程序，用从 OPENROWSET 返回的结果填充一个表，并且为批量加载操作指定表选项。例如，以下代码将上一个查询的结果加载到表 MyTable 中，并请求禁用目标表中的约束检查：
INSERT INTO MyTable WITH (IGNORE_CONSTRAINTS) SELECT col1, col2, col3 FROM OPENROWSET(BULK 'c:\temp\textfile1.txt', FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)
除了 IGNORE_CONSTRAINTS 选项以外，可以在加载操作中指定的其他表提示包括：BULK_KEEPIDENTITY、BULK_KEEPNULLS 和 IGNORE_TRIGGERS。
您还可以使用 BULK 提供程序，通过指定下列选项之一，将文件数据作为某个大型对象类型的单个列值返回：用于字符数据的 SINGLE_CLOB、用于 Unicode 数据的 SINGLE_NCLOB 以及用于二进制数据的 SINGLE_BLOB。当您使用上述选项之一时，您没有指定格式文件。您可以将文件加载（使用 INSERT 或 UPDATE 语句）到下列数据类型之一的大型对象列中：VARCHAR(MAX)、NVARCHAR(MAX)、VARBINARY(MAX) 或 XML。在下文中，您可以找到有关变长列的 MAX 说明符以及有关 XML 数据类型的详细信息。
作为将文件加载到大型列中的示例，以下 UPDATE 语句将文本文件“c:\temp\textfile101.txt”加载到客户 101 的表 CustomerData 中的列 txt_data 中：
UPDATE CustomerData SET txt_data = (SELECT txt_data FROM OPENROWSET( BULK 'c:\temp\textfile101.txt', SINGLE_CLOB) AS F(txt_data)) WHERE custid = 101
请注意，一次只能更新一个大型列。
以下示例说明了如何使用 INSERT 语句将客户 102 的二进制文件加载到大型列中：
INSERT INTO CustomerData(custid, binary_data) SELECT 102 AS custid, binary_data FROM OPENROWSET( BULK 'c:\temp\binfile102.dat', SINGLE_BLOB) AS F(binary_data)
异常处理
SQL Server 2005 Beta 2 以 TRY...CATCH Transact-SQL 结构的形式引入了一种简单但非常强大的异常处理机制。
以前版本的 SQL Server 要求在每个怀疑可能出错的语句之后包含错误处理代码。要将错误检查代码集中在一起，必须使用标签和 GOTO 语句。此外，诸如数据类型转换错误之类的错误会导致批处理终止；因此，无法用 Transact-SQL 捕获这些错误。SQL Server 2005 Beta 2 解决了这些问题中的很多问题。
现在可以捕获和处理过去会导致批处理终止的错误，前提是这些错误不会导致连接中断（通常是严重度为 21 及以上的错误，例如，表或数据库完整性可疑、硬件错误等等）。
在 BEGIN TRY/END TRY 块中编写您希望执行的代码，并且后面紧跟位于 BEGIN CATCH/END CATCH 块中的错误处理代码。请注意，TRY 块必须具有相应的 CATCH 块；否则，您将得到语法错误。作为一个简单的示例，请考虑以下 Employees 表：
CREATE TABLE Employees ( empid INT NOT NULL, empname VARCHAR(25) NOT NULL, mgrid INT NULL, /* other columns */ CONSTRAINT PK_Employees PRIMARY KEY(empid), CONSTRAINT CHK_Employees_empid CHECK(empid > 0), CONSTRAINT FK_Employees_Employees FOREIGN KEY(mgrid) REFERENCES Employees(empid) )
您希望编写代码以便将新的雇员行插入到该表中。您还希望用一些纠正性的活动响应失败情况。按如下方式使用新的 TRY...CATCH 结构：
BEGIN TRY INSERT INTO Employees(empid, empname, mgrid) VALUES(1, 'Emp1', NULL) PRINT 'After INSERT.' END TRY BEGIN CATCH PRINT 'INSERT failed.' /* perform corrective activity */ END CATCH
当您首次运行该代码时，应当获得输出“After INSERT”。当您第二次运行它时，应当获得输出“INSERT Failed”。
如果 TRY 块中的代码没有任何错误地完成，则控制被传递给相应的 CATCH 块后面的第一个语句。当 TRY 块中的语句失败时，控制被传递给相应的 CATCH 块中的第一个语句。请注意，如果错误被 CATCH 块捕获，则它不会返回到调用应用程序。如果您还希望应用程序获得错误信息，则必须自己将该信息提供给应用程序（例如，使用 RAISERROR 或作为查询的结果集）。所有错误信息都借助于四个新的函数在 CATCH 块中提供给您：ERROR_NUMBER()、ERROR_MESSAGE()、ERROR_SEVERITY() 和 ERROR_STATE()。这些函数可以在 CATCH 块中您喜欢的任何位置多次查询，并且它们的值保持不变。这与除 DECLARE 以外还受到任何语句影响的 @@error 函数（因此必须在 CATCH 块的第一个语句中查询它）相反。ERROR_NUMBER() 可以用作 @@error 的替代函数，而其他三个函数则完全按照由错误生成的样子向您提供该信息的其余部分。此类信息在低于 SQL Server 2005 的 SQL Server 版本中无法获得。
如果在批处理或例程（存储过程、触发器、用户定义的函数、动态代码）中生成了未处理的错误，并且某个较高级别的代码在 TRY 块内部调用了该批处理或例程，则控制被传递给该较高级别的相应 CATCH 块。如果该较高级别没有在 TRY 块内调用该内部级别，则 SQL Server 将继续在调用堆栈中的较高级别中查找 TRY 块，并且会将控制传递给找到的第一个 TRY...CATCH 结构的 CATCH 块。如果未找到，则将错误返回给调用应用程序。
作为一个更详细的示例，以下代码根据导致失败的错误的类型做出不同的反应，并且输出消息以表明代码的哪些部分已经被激活：
PRINT 'Before TRY...CATCH block.' BEGIN TRY PRINT ' Entering TRY block.' INSERT INTO Employees(empid, empname, mgrid) VALUES(2, 'Emp2', 1) PRINT ' After INSERT.' PRINT ' Exiting TRY block.' END TRY BEGIN CATCH PRINT ' Entering CATCH block.' IF ERROR_NUMBER() = 2627 BEGIN PRINT ' Handling PK violation...' END ELSE IF ERROR_NUMBER() = 547 BEGIN PRINT ' Handling CHECK/FK constraint violation...' END ELSE IF ERROR_NUMBER() = 515 BEGIN PRINT ' Handling NULL violation...' END ELSE IF ERROR_NUMBER() = 245 BEGIN PRINT ' Handling conversion error...' END ELSE BEGIN PRINT ' Handling unknown error...' END PRINT ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) PRINT ' Error Message: ' + ERROR_MESSAGE() PRINT ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) PRINT ' Error State : ' + CAST(ERROR_STATE() AS VARCHAR(10)) PRINT ' Exiting CATCH block.' END CATCH PRINT 'After TRY...CATCH block.'
请注意，ERROR_NUMBER() 函数在 CATCH 块中被多次调用，并且它总是返回导致控制传递给该 CATCH 块的错误的编号。该代码将雇员 2 作为先前插入的雇员 1 的下属插入，并且在首次运行时应当没有任何错误地完成，并生成以下输出：
Before TRY...CATCH block. Entering TRY block. After INSERT. Exiting TRY block. After TRY...CATCH block.
请注意，CATCH 块被跳过。第二次运行该代码时，应当生成以下输出：
Before TRY...CATCH block. Entering TRY block. Entering CATCH block. Handling PK violation... Error Number: 2627 Error Message: Violation of PRIMARY KEY constraint 'PK_Employees'. Cannot insert duplicate key in object 'Employees'. Error Severity: 14 Error State : 1 Exiting CATCH block. After TRY...CATCH block.
请注意，TRY 块被进入，但未完成。作为主键冲突错误的结果，控制被传递给 CATCH 块，该块会识别并处理该错误。类似地，如果您分配的值不是有效的雇员 ID 数据，例如，0（它违反了 CHECK 约束）、NULL（它不允许在 employeeid 中使用）以及 'a,'（它无法转换为 INT），则您会得到相应的错误，并且会激活相应的处理代码。
如果您要在 TRY 块中使用显式事务，则您可能希望在 CATCH 块中的错误处理代码中调查事务状态，以确定操作过程。SQL Server 2005 提供了新的函数 XACT_STATE() 以返回事务状态。该函数可能返回的值为：0、-1 和 1。0 返回值意味着没有打开任何事务。试图提交或回滚该事务时，会生成错误。1 返回值意味着事务已打开，并且可以提交或回滚。您需要根据自己的需要和错误处理逻辑确定是提交还是回滚该事务。-1 返回值意味着事务已打开但处于无法提交的状态 — 这是 SQL Server 2005 中引入的新的事务状态。当生成可能会导致事务被中止的错误（通常，严重度为 17 或更高）时，TRY 块内的事务会进入无法提交的状态。无法提交的事务会保持所有打开的锁，并且只允许您读取数据。您不能提交任何需要写事务日志的活动，这意味着当事务处于无法提交的状态时，您无法更改数据。为了终止该事务，您必须发出回滚。您不能提交该事务，而只能在可以接受任何修改之前将其回滚。以下示例演示了如何使用 XACT_STATE() 函数：
BEGIN TRY BEGIN TRAN INSERT INTO Employees(empid, empname, mgrid) VALUES(3, 'Emp3', 1) /* other activity */ COMMIT TRAN PRINT 'Code completed successfully.' END TRY BEGIN CATCH PRINT 'Error: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) + ' found.' IF (XACT_STATE()) = -1 BEGIN PRINT 'Transaction is open but uncommittable.' /* ...investigate data... */ ROLLBACK TRANSACTION -- can only ROLLBACK /* ...handle the error... */ END ELSE IF (XACT_STATE()) = 1 BEGIN PRINT 'Transaction is open and committable.' /* ...handle error... */ COMMIT TRANSACTION -- or ROLLBACK END ELSE BEGIN PRINT 'No open transaction.' /* ...handle error... */ END END CATCH
TRY 块在显式事务内部提交代码。它插入一个新的雇员行，并且在同一事务内部执行其他一些活动。CATCH 块输出错误编号，并且调查事务状态以确定操作过程。如果事务已打开并且无法提交，则 CATCH 块会调查数据，回滚该事务，然后采取任何需要数据修改的纠正性措施。如果该事务已打开并且可以提交，则 CATCH 块会处理错误并提交（也可能回滚）。如果没有任何事务打开，则错误被处理。不会发出任何提交或回滚。如果您是首次运行该代码，则会插入对应于雇员 3 的新的雇员行，并且代码成功完成，产生以下输出：
Code completed successfully.
如果您是第二次运行该代码，则会生成主键冲突错误，并且您会获得以下输出：
Error: 2627 found. Transaction is open and committable.
其他影响 Transact-SQL 的 SQL Server 2005 Beta 2 功能
本节简要描述 SQL Server 2005 Beta 2 中的其他影响 Transact-SQL 的增强功能。这包括对 TOP 进行的增强、带结果的数据操纵语言 (DML)、动态列的 MAX 说明符、XML/XQuery、数据定义语言 (DDL) 触发器、队列和 SQL Server Service Broker 以及 DML 事件和通知。
TOP 增强功能
在 SQL Server 版本 7.0 和 SQL Server 2000 中，可以通过 TOP 选项限制 SELECT 查询所返回的行数或百分比；但是，您必须提供一个常量作为参数。在 SQL Server 2005 Beta 2 中，TOP 用下列主要方式进行了增强：
| • |
现在可以指定一个数字表达式，以返回要通过查询影响的行数或百分比，还可以根据情况使用变量和子查询。 |
| • |
现在可以在 DELETE、UPDATE 和 INSERT 查询中使用 TOP 选项。 |
使用 TOP 选项的查询的新语法是：
SELECT [TOP () [PERCENT] [WITH TIES]] FROM ...[ORDER BY...] DELETE [TOP () [PERCENT]] FROM ... UPDATE [TOP () [PERCENT]] SET ... INSERT [TOP () [PERCENT]] INTO ...
必须在括号中指定数字表达式。在 SELECT 查询中支持不用括号指定常量的原因是为了保持向后兼容。表达式必须是独立的 — 如果您使用子查询，则它无法引用外部查询中的表的列。如果您不指定 PERCENT 选项，则该表达式必须可以隐式转换为 bigint 数据类型。如果您指定 PERCENT 选项，则该表达式必须可以隐式转换为 float 并且落在范围 0 到 100 之内。WITH TIES 选项和 ORDER BY 子句只在 SELECT 查询中受到支持。
例如，以下代码使用变量作为 TOP 选项的参数，并且返回指定数量的最新购买定单：
USE AdventureWorks DECLARE @n AS BIGINT SET @n = 2 SELECT TOP(@n) * FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate DESC
当您将所请求的行的数量作为存储过程或用户定义函数的参数时，该增强功能尤其有用。通过使用独立的子查询，您可以回答动态请求，例如，“计算每月定单的平均数量，并返回那么多的最新定单”：
USE AdventureWorks SELECT TOP(SELECT COUNT(*)/DATEDIFF(month, MIN(OrderDate), MAX(OrderDate)) FROM Purchasing.PurchaseOrderHeader) * FROM Purchasing.PurchaseOrderHeader ORDER BY OrderDate DESC
较低版本的 SQL Server 中的 SET ROWCOUNT 选项使您可以限制受到查询影响的行数。例如，SET ROWCOUNT 常用来定期清除多个小型事务而不是单个大型事务中的大量数据：
SET ROWCOUNT 1000 DELETE FROM BigTable WHERE datetimecol < '20000101' WHILE @@rowcount > 0 DELETE FROM BigTable WHERE datetimecol < '20000101' SET ROWCOUNT 0
以该方式使用 SET ROWCOUNT，可以在清除过程中备份和回收事务日志，并且还可以防止锁升级。现在可以这样使用 TOP，而不是使用 SET ROWCOUNT：
DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101' WHILE @@rowcount > 0 DELETE TOP(1000) FROM BigTable WHERE datetimecol < '20000101'
当您使用 TOP 选项时，优化程序可以知道“行目标”是什么以及到底是否使用了 TOP，从而使优化程序可以产生更有效的计划。
尽管您可能认为不需要在 INSERT 语句中使用 TOP（因为您总是可以在 SELECT 查询中指定它），但您可能会发现它在插入 EXEC 命令的结果或 UNION 操作的结果时很有用。例如：
INSERT TOP ... INTO ... EXEC ... INSERT TOP ... INTO ... SELECT ... FROM T1 UNION ALL SELECT ... FROM T2 ORDER BY ...
带结果的 DML
SQL Server 2005 引入了一个新的 OUTPUT 子句，以使您可以从修改语句（INSERT、UPDATE、DELETE）中将数据返回到表变量中。带结果的 DML 的有用方案包括清除和存档、消息处理应用程序以及其他方案。这一新的 OUTPUT 子句的语法为：
OUTPUT <dml_select_list> INTO @table_variable
可以通过引用插入的表和删除的表来访问被修改的行的旧/新映像，其方式与访问触发器类似。在 INSERT 语句中，只能访问插入的表。在 DELETE 语句中，只能访问删除的表。在 UPDATE 语句中，可以访问插入的表和删除的表。
作为带结果的 DML 可能有用的清除和存档方案的示例，假设您具有一个大型的 Orders 表，并且您希望定期清除历史数据。您还希望将清除的数据复制到一个名为 OrdersArchive 的存档表中。您声明了一个名为 @DeletedOrders 的表变量，并且进入一个循环，在该循环中，您使用上文中的“TOP 增强功能”一节中描述的清除方法，成块地删除了历史数据（比如，早于 2003 年的定单）。这里增加的代码是 OUTPUT 子句，它将所有被删除的行的所有属性复制到 @DeletedOrders 表变量中，然后，使用 INSERT INTO 语句将该表变量中的所有行复制到 OrdersArchive 表中：
DECLARE @DeletedOrders TABLE ( orderid INT, orderdate DATETIME, empid INT, custid VARCHAR(5), qty INT ) WHILE 1=1 BEGIN BEGIN TRAN DELETE TOP(5000) FROM Orders OUTPUT deleted.* INTO @DeletedOrders WHERE orderdate < '20030101' INSERT INTO OrdersArchive SELECT * FROM @DeletedOrders COMMIT TRAN DELETE FROM @DeletedOrders IF @@rowcount < 5000 BREAK END
作为消息处理方案的示例，请考虑以下 Messages 表：
USE tempdb
CREATE TABLE Messages
(
msgid INT NOT NULL IDENTITY ,
msgdate DATETIME NOT NULL DEFAULT(GETDATE()),
msg VARCHAR(MAX) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT('new'),
CONSTRAINT PK_Messages
PRIMARY KEY NONCLUSTERED(msgid),
CONSTRAINT UNQ_Messages_status_msgid
UNIQUE CLUSTERED(status, msgid),
CONSTRAINT CHK_Messages_status
CHECK (status IN('new', 'open', 'done'))
)
对于每个消息，您都存储了消息 ID、条目日期、消息文本以及表明该消息尚未处理（“new”）、正在处理（“open”）还是已经处理（“done”）的状态。
以下代码模拟了一个会话，该会话通过使用一个每秒钟用随机文本插入消息的循环生成消息。刚刚插入的消息的状态为“new”，因为状态列被分配了默认值“new”。同时从多个会话中运行该代码：
USE tempdb
SET NOCOUNT ON
DECLARE @msg AS VARCHAR(MAX)
WHILE 1=1
BEGIN
SET @msg = 'msg' + RIGHT('000000000'
+ CAST(CAST(RAND()*2000000000 AS INT)+1 AS VARCHAR(10)), 10)
INSERT INTO dbo.Messages(msg) VALUES(@msg)
WAITFOR DELAY '00:00:01';
END
以下代码模拟一个会话，该会话使用下列步骤处理消息：
|
1. |
构建一个不断处理消息的无限循环。 |
|
2. |
使用 UPDATE TOP(1) 语句锁定一个可用的新消息（用 READPAST 提示跳过被锁定的行），并且将它的状态更改为“open”。 |
|
3. |
使用 OUTPUT 子句在 @Msgs 表变量中存储消息属性。 |
|
4. |
处理该消息。 |
|
5. |
通过合并 Messages 表和 @Msgs 表变量，将消息状态设置为“done”。 |
|
6. |
如果没有在 Messages 表中找到新的消息，等待一秒钟。 |
从多个会话中运行该代码：
USE tempdb SET NOCOUNT ON DECLARE @Msgs TABLE(msgid INT, msgdate DATETIME, msg VARCHAR(MAX)) WHILE 1 = 1 BEGIN UPDATE TOP(1) Messages WITH(READPAST) SET status = 'open' OUTPUT inserted.msgid, inserted.msgdate, inserted.msg INTO @Msgs WHERE status = 'new' IF @@rowcount > 0 BEGIN PRINT 'Processing message...' -- process message here SELECT * FROM @msgs UPDATE M SET status = 'done' FROM Messages AS M JOIN @Msgs AS N ON M.msgid = N.msgid; DELETE FROM @Msgs END ELSE BEGIN PRINT 'No messages to process.' WAITFOR DELAY '00:00:01' END END
在运行完该模拟之后，立即停止所有插入和处理消息的会话，并且删除 Messages 表：
USE tempdb DROP TABLE Messages
动态列的 MAX 说明符
SQL Server 2005 通过使用语法 (MAX) 引入 MAX 说明符，增强了变长数据类型 VARCHAR、NVARCHAR 和 VARBINARY 的能力。带有 MAX 说明符的变长数据类型用增强功能取代了数据类型 TEXT、NTEXT 和 IMAGE。使用带有 MAX 说明符的变长数据类型作为大型对象数据类型 TEXT、NTEXT 和 IMAGE 的替代类型有多个优点。无须使用显式指针操作，因为 SQL Server 在内部确定何时以内联方式存储值以及何时使用指针。您现在能够对小型和大型数据使用统一的编程模型。带有 MAX 说明符的变长数据类型受到列、变量、参数、比较、触发器和所有字符串函数等的支持。
作为使用 MAX 说明符的示例，以下代码创建了一个名为 CustomerData 的表：
CREATE TABLE CustomerData ( custid INT NOT NULL PRIMARY KEY, txt_data VARCHAR(MAX) NULL, ntxt_data NVARCHAR(MAX) NULL, binary_data VARBINARY(MAX) NULL )
该表包含列 custid（该列被用作主键）以及可为空值的列 txt_data、ntxt_data 和 binary_data（它们分别用数据类型 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX) 定义，可以存储大型数据）。
为了从带有 MAX 说明符的动态列中读取块，可以按照与常规动态列相同的方式使用 SUBSTRING 函数。为了更新块，可以使用 UPDATE 语句的增强语法，它现在提供了 WRITE 方法。增强的 UPDATE 语句的语法为：
UPDATE table_name SET column_name.WRITE(@chunk, @offset, @len) WHERE ...
WRITE 方法从 @offset 位置移除 @len 字符，并且在该位置插入 @chunk。请注意，@offset 是从零开始的，意味着偏移量 0 表示 @chunk 中的第一个字符的位置。为了演示 WRITE 方法的用法，请首先用客户 ID 102 和 txt_data 列中的值“Customer 102 text data”在 CustomerData 表中插入一个行：
INSERT INTO CustomerData(custid,txt_data) VALUES(102, 'Customer 102 text data')
以下 UPDATE 语句将“102”替换为“one hundred and two”：
UPDATE CustomerData
SET txt_data.WRITE('one hundred and two', 9, 3)
WHERE custid = 102
当 @chunk 为 NULL 时，@len 被忽略，并且值在 @offset 位置截断。以下语句移除了从偏移量 28 直到结尾的所有数据：
UPDATE CustomerData SET txt_data.WRITE(NULL, 28, 0) WHERE custid = 102
当 @len 为 NULL 时，从 @offset 到结尾的所有字符都被移除，并且 @chunk 被追加。以下语句移除了从偏移量 9 直到结尾的所有数据，并且追加了“102”：
UPDATE CustomerData
SET txt_data.WRITE('102', 9, NULL)
WHERE custid = 102
当 @offset 为 NULL 时，@len 被忽略，并且在结尾追加了 @chunk。以下语句在结尾追加了字符串“ is discontinued”：
UPDATE CustomerData
SET txt_data.WRITE(' is discontinued', NULL, 0)
WHERE custid = 102
XML 和 XQuery
SQL Server 2005 Beta 2 引入了多个与 XML 相关的增强功能，以使您可以自然地存储、查询和更新 XML 结构化数据。您可以在同一数据库中存储 XML 和关系数据，并且利用现有的数据库引擎进行存储和查询处理。
引入了一个新的 xml 数据类型。xml 数据类型可以用于表列，甚至可以进行索引。xml 数据类型还可以在变量、视图、函数和存储过程中使用。可以通过关系 FOR XML 查询生成 xml 数据类型，或者使用 OPENXML 将其作为关系行集进行访问。可以将架构导入到数据库中，或者从数据库中导出架构。可以使用架构来验证和约束 XML 数据。可以通过使用 XQuery 查询和修改 XML 类型化数据。xml 数据类型在触发器、复制、批量复制、DBCC 和全文搜索中受到支持。但是，xml 是不可比较的，这意味着您无法在 xml 列上定义 PRIMARY KEY、UNIQUE 或 FOREIGN KEY 约束。
下列示例使用了 xml 数据类型。以下代码定义了一个名为 @x 的 XML 变量，并且将客户定单数据加载到该变量中：
USE AdventureWorks DECLARE @x AS XML SET @x = (SELECT C.CustomerID, O.SalesOrderID FROM Sales.Customer C JOIN Sales.SalesOrderHeader O ON C.CustomerID=O.CustomerID ORDER BY C.CustomerID FOR XML AUTO, TYPE) SELECT @x
以下代码创建了一个带有 xml 列的表，并且通过使用 OPENROWSET 函数将一个 XML 文件批量加载到该表中：
CREATE TABLE T1 ( keycol INT NOT NULL PRIMARY KEY, xmldoc XML NULL ) INSERT INTO T1(keycol, xmldoc) SELECT 1 AS keycol, xmldoc FROM OPENROWSET(BULK 'C:\documents\mydoc.xml', SINGLE_NCLOB) AS X(xmldoc)
SQL Server 2005 Beta 2 还引入了对 XQuery（它是一个 W3C 标准 XML 查询语言）的支持。Microsoft 在 SQL Server 中为该标准提供了扩展，以允许使用 XQuery 进行插入、更新和删除。XQuery 借助于用户定义类型 (UDT) 样式方法嵌入在 Transact-SQL 中。
XQuery 提供了下列查询方法：
| • |
操纵 XML 数据：@x.query (xquery string) 返回 XML |
| • |
检查存在性：@x.exist (xquery string) 返回位 |
| • |
返回标量值 @x.value (xquery string, sql_type string) 返回 sql_type |
XQuery 提供了以下修改方法：@x.modify (xDML string)。
作为示例，一个名为 Jobs 的表在一个名为 jobinfo 的列中包含 XML 格式的作业信息。以下查询在执行一些操作之后返回每个符合某个条件的行的 ID 和 XML 数据。在 WHERE 子句中调用方法 jobinfo.exist() 以筛选所需的行。对于在 jobinfo 列中包含被编辑的元素且该元素的 date 属性大于 Transact-SQL 变量 @date 的行，它只返回 1。对于所返回的每个行，通过调用 jobinfo.query() 方法生成 XML 结果。对于在 jobinfo 中找到的每个作业元素，query() 方法生成一个 jobschedule 元素（它的 id 属性基于该作业的 id 属性）以及 begin 和 end 子元素（它们的数据基于 jobinfo 中的 start 和 end 属性）：
SELECT id, jobinfo.query(
'for $j in //job
return
<jobschedule id="{$j/@id}">
<begin>{data($j/@start)}</begin>
<end>{data($j/@end)}</end>
</jobschedule>')
FROM Jobs
WHERE 1 = jobinfo.exist(
'//edited[@date > sql:variable("@date")]')
以下 value() 方法调用以 Transact-SQL datetime 格式返回 jobinfo 中的第一个作业的 start 属性：
SELECT id, jobinfo.value('(//job)[1]/@start', 'DATETIME') AS startdt
FROM Jobs
WHERE id = 1
XQuery 还可以用来修改数据。例如，可以使用以下代码为雇员 1 更新 Employees 表中的 empinfo XML 列。将 resume 元素的被编辑的子元素的 date 属性更新为新的值：
UPDATE Employees SET empinfo.modify(
'update /resume/edited/@date
to xs:date("2000-6-20")')
WHERE empid = 1
DDL 触发器
在较低版本的 SQL Server 中，只能为针对表发出的 DML 语句（INSERT、UPDATE 和 DELETE）定义 AFTER 触发器。SQL Server 2005 Beta 2 使您可以就整个服务器或数据库的某个范围为 DDL 事件定义触发器。您可以为单个 DDL 语句（例如，CREATE_TABLE）或者为一组语句（例如，DDL_DATABASE_LEVEL_EVENTS）定义 DDL 触发器。在该触发器内部，您可以通过访问 eventdata() 函数获得与激发该触发器的事件有关的数据。该函数返回有关事件的 XML 数据。每个事件的架构都继承了 Server Events 基础架构。
事件信息包括：
| • |
事件何时发生。 |
| • |
事件是从哪个 SPID 中发出的。 |
| • |
事件的类型。 |
| • |
受影响的对象。 |
| • |
SET 选项。 |
| • |
激发它的 Transact-SQL 语句。 |
与较低版本的 SQL Server 中的触发器类似，DDL 触发器在激发它们的事务的上下文中运行。如果您决定撤消激发触发器的事件，则可以发出一个 ROLLBACK 语句。例如，以下触发器可防止在当前数据库中创建新表：
CREATE TRIGGER trg_capture_create_table ON DATABASE FOR CREATE_TABLE
AS
-- PRINT event information For DEBUG
PRINT 'CREATE TABLE Issued'
PRINT EventData()
-- Can investigate data returned by EventData() and react accordingly.
RAISERROR('New tables cannot be created in this database.', 16, 1)
ROLLBACK
GO
如果您在创建了该触发器的数据库中发出 CREATE TABLE 语句，则应当获得以下输出：
CREATE TABLE T1(col1 INT) CREATE TABLE Issued <EVENT_INSTANCE> <PostTime>2003-04-17T13:55:47.093</PostTime> <SPID>53</SPID> <EventType>CREATE_TABLE</EventType> <Database>testdb</Database> <Schema>dbo</Schema> <Object>T1</Object> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>CREATE TABLE T1(col1 INT)</CommandText> </TSQLCommand> </EVENT_INSTANCE> .Net SqlClient Data Provider: Msg 50000, Level 16, State 1, Procedure trg_capture_create_table, Line 10 New tables cannot be created in this database. .Net SqlClient Data Provider: Msg 3609, Level 16, State 1, Line 1 Transaction ended in trigger. Batch has been aborted.
请注意，本文对 XML 输出进行了手动格式化，以便提高可读性。当您运行该代码时，会获得未经格式化的 XML 输出。
要删除该触发器，请发出以下语句：
DROP TRIGGER trg_capture_create_table ON DATABASE
DDL 触发器特别有用的方案包括 DDL 更改的完整性检查、审核方案以及其他方案。作为 DDL 完整性增强的示例，以下数据库级别触发器拒绝了创建不带主键的表的企图：
CREATE TRIGGER trg_create_table_with_pk ON DATABASE FOR CREATE_TABLE
AS
DECLARE @eventdata AS XML, @objectname AS NVARCHAR(257),
@msg AS NVARCHAR(500)
SET @eventdata = eventdata()
SET @objectname =
N'[' + CAST(@eventdata.query('data(//SchemaName)') AS SYSNAME)
+ N'].[' +
CAST(@eventdata.query('data(//ObjectName)') AS SYSNAME) + N']'
IF OBJECTPROPERTY(OBJECT_ID(@objectname), 'TableHasPrimaryKey') = 0
BEGIN
SET @msg = N'Table ' + @objectname + ' does not contain a primary key.'
+ CHAR(10) + N'Table creation rolled back.'
RAISERROR(@msg, 16, 1)
ROLLBACK
RETURN
END
当 CREATE TABLE 语句发出时，该触发器被激发。该触发器使用 XQuery 提取架构和对象名称，并且使用 OBJECTPROPERTY 函数检查该表是否包含主键。如果不包含，则该触发器会生成错误并回滚事务。在创建该触发器之后，以下创建不带主键的表的尝试将失败：
CREATE TABLE T1(col1 INT NOT NULL) Msg 50000, Level 16, State 1, Procedure trg_create_table_with_pk, Line 19 Table [dbo].[T1] does not contain a primary key. Table creation rolled back. Msg 3609, Level 16, State 2, Line 1 Transaction ended in trigger. Batch has been aborted.
而以下尝试将成功：
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY)
要删除该触发器和表 T1，请运行以下代码：
DROP TRIGGER trg_create_table_with_pk ON DATABASE DROP TABLE T1
作为审核触发器的示例，以下数据库级别触发器针对 AuditDDLEvents 表审核所有 DDL 语句：
CREATE TABLE AuditDDLEvents
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
schemaname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
targetobjectname SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLEvents PRIMARY KEY(LSN)
)
GO
CREATE TRIGGER trg_audit_ddl_events ON DATABASE FOR DDL_DATABASE_LEVEL_EVENTS
AS
DECLARE @eventdata AS XML
SET @eventdata = eventdata()
INSERT INTO dbo.AuditDDLEvents(
posttime, eventtype, loginname, schemaname,
objectname, targetobjectname, eventdata)
VALUES(
CAST(@eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@eventdata.query('data(//SchemaName)') AS SYSNAME),
CAST(@eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@eventdata.query('data(//TargetObjectName)') AS SYSNAME),
@eventdata)
GO
该触发器简单地使用 XQuery 从 eventdata() 函数中提取所有感兴趣的事件属性，并且将这些属性插入到 AuditDDLEvents 表中。要测试该触发器，请提交几个 DDL 语句并查询审核表：
CREATE TABLE T1(col1 INT NOT NULL PRIMARY KEY) ALTER TABLE T1 ADD col2 INT NULL ALTER TABLE T1 ALTER COLUMN col2 INT NOT NULL CREATE NONCLUSTERED INDEX idx1 ON T1(col2) SELECT * FROM AuditDDLEvents
要检查都有谁在过去 24 小时中更改了表 T1 的架构以及他们是如何更改的，请运行以下查询：
SELECT posttime, eventtype, loginname,
CAST(eventdata.query('data(//TSQLCommand)') AS NVARCHAR(2000))
AS tsqlcommand
FROM dbo.AuditDDLEvents
WHERE schemaname = N'dbo' AND N'T1' IN(objectname, targetobjectname)
ORDER BY posttime
要删除该触发器和刚刚创建的表，请运行以下代码：
DROP TRIGGER trg_audit_ddl_events ON DATABASE DROP TABLE dbo.T1 DROP TABLE dbo.AuditDDLEvents
作为服务器级别审核触发器的示例，以下触发器审核了到达一个名为 AuditDDLLogins 的审核表的所有与 DDL 登录相关的事件：
USE master
CREATE TABLE dbo.AuditDDLLogins
(
LSN INT NOT NULL IDENTITY,
posttime DATETIME NOT NULL,
eventtype SYSNAME NOT NULL,
loginname SYSNAME NOT NULL,
objectname SYSNAME NOT NULL,
logintype SYSNAME NOT NULL,
eventdata XML NOT NULL,
CONSTRAINT PK_AuditDDLLogins PRIMARY KEY(LSN)
)
CREATE TRIGGER audit_ddl_logins ON ALL SERVER
FOR DDL_LOGIN_EVENTS
AS
DECLARE @eventdata AS XML
SET @eventdata = eventdata()
INSERT INTO master.dbo.AuditDDLLogins(
posttime, eventtype, loginname,
objectname, logintype, eventdata)
VALUES(
CAST(@eventdata.query('data(//PostTime)') AS VARCHAR(23)),
CAST(@eventdata.query('data(//EventType)') AS SYSNAME),
CAST(@eventdata.query('data(//LoginName)') AS SYSNAME),
CAST(@eventdata.query('data(//ObjectName)') AS SYSNAME),
CAST(@eventdata.query('data(//LoginType)') AS SYSNAME),
@eventdata)
GO
要测试该触发器，请发出下列 DDL 登录语句以创建、改变和退出登录，然后查询审核表：
CREATE LOGIN login1 WITH PASSWORD = '123' ALTER LOGIN login1 WITH PASSWORD = 'xyz' DROP LOGIN login1 SELECT * FROM AuditDDLLogins
要退出该触发器和审核表，请运行以下代码：
DROP TRIGGER audit_ddl_logins ON ALL SERVER DROP TABLE dbo.AuditDDLLogins DROP DATABASE testdb
DDL 和系统事件通知
SQL Server 2005 Beta 2 使您可以捕获 DDL 和系统事件，并且向 Service Broker 部署发送事件通知。尽管触发器被同步处理，但事件通知是一种允许异步使用的事件传递机制。事件通知将 XML 数据发送给指定的 Service Broker 服务，而事件使用者异步使用该数据。事件使用者可以使用 WAITFOR 子句的扩展等待新数据到达。
事件通知通过下列元素定义：
| • |
范围（SERVER、DATABASE、ASSEMBLY、单个对象） |
| • |
事件或事件组的列表（例如，CREATE_TABLE、DDL_EVENTS 等等） |
| • |
实现 SQL Server Events 消息类型和协定的部署名称 |
事件数据是使用 SQL Server Events 架构以 XML 格式发送的。用于创建事件通知的常规语法是：
CREATE EVENT NOTIFICATION <name> ON <scope> FOR <list_of_event_or_event_groups> TO SERVICE <deployment_name>
当事件通知被创建时，会在系统部署和由用户指定的部署之间建立 Service Broker 对话。 指定相应的 Service Broker，以便 SQL Server 为其打开对话以传递有关事件的数据。指定的部署必须实现 SQL Server Events 消息类型和协定。当发生存在相应的事件通知的事件时，会根据有关的事件数据构建一个 XML 消息，并且通过该事件通知的对话将其发送到指定的部署。
例如，以下代码创建了一个名为 T1 的表，并且定义了一个事件通知，以便每当 T1 表的架构改变时向特定的部署发送通知：
CREATE TABLE dbo.T1(col1 INT); GO -- Create a queue. CREATE QUEUE SchemaChangeQueue; GO --Create a service on the queue that references --the event notifications conract. CREATE SERVICE SchemaChangeService ON QUEUE SchemaChangeQueue ( [//s.ms.net/SQL/Notifications/PostEventNotification/v1.0] ); GO --Create a route on the service to define the address --to which Service Broker sends messages for the service. CREATE ROUTE SchemaChangeRoute WITH SERVICE_NAME = 'SchemaChangeService', ADDRESS = 'LOCAL'; GO --Create the event notification. CREATE EVENT NOTIFICATION NotifySchemaChangeT1 ON TABLE dbo.T1 FOR ALTER_TABLE TO SERVICE [SchemaChangeService];
而以下 ALTER 则会导致向 SchemaChangeService（它是在 SchemaChangeQueue 之上生成的）发送一个 XML 消息：
ALTER TABLE dbo.T1 ADD col2 INT;
然后，可以用以下语句从该队列中检索 XML 消息：
RECEIVE TOP (1) CAST(message_body AS nvarchar(MAX)) FROM SchemaChangeQueue
产生的输出将如下所示（无格式化）：
<EVENT_INSTANCE> <PostTime>2004-06-15T11:16:32.963</PostTime> <SPID>55</SPID> <EventType>ALTER_TABLE</EventType> <ServerName>MATRIX\S1</ServerName> <LoginName>MATRIX\Gandalf</LoginName> <UserName>MATRIX\Gandalf</UserName> <DatabaseName>testdb</DatabaseName> <SchemaName>dbo</SchemaName> <ObjectName>T1</ObjectName> <ObjectType>TABLE</ObjectType> <TSQLCommand> <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" /> <CommandText>ALTER TABLE dbo.T1 ADD col2 INT;</CommandText> </TSQLCommand> </EVENT_INSTANCE>
WAITFOR 语句可以用来以阻塞模式接收通知，如下所示：
WAITFOR (RECEIVE * FROM myQueue)
小结
SQL Server 2005 Beta 2 中的 Transact-SQL 增强功能提高了您在编写查询时的表达能力，使您可以改善代码的性能，并且扩充了您的错误管理能力。Microsoft 在增强 Transact-SQL 方面不断付出的努力显示了对它在 SQL Server 中具有的重要作用、它的威力以及它的将来所怀有的坚定信念。
posted on 2008-07-04 17:54 Davidhuang 阅读(341) 评论(0) 编辑 收藏 举报
