假设您希望呈现 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'