新一篇: 正则表达式使用详解
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:52 Davidhuang 阅读(948) 评论(0) 编辑 收藏 举报
