错误处理 SNAPSHOT 隔离 WAITFOR 命令 BULK 行集提供程序 TOP 选项 APPLY 运算符 通用表表达式和递归查询 PIVOT 运算符 触发器和通知 小结

sql server 下一版本 — 代号“Yukon”的 Beta 1 版引入了许多对 T-SQL 的功能增强和新增功能，可以提高您的表达能力、错误管理水平和性能。本文中，我将讨论一些重要的功能增强和新功能，包括错误处理、递归查询和隔离。我将把注意力主要放在错误管理和相关的改进，并简要描述其他方面的功能增强。www.iTbulo.com-ZO8xOW1

除了这里叙述的功能以外，T-SQL 还有许多其他重要功能增强我没有深入探讨，因为其中一些从概念上讲并不新，而另一些又需要单独讨论。这些方面包括消息处理和服务代理平台，分区和 XML 的功能增强。（有关 Yukon 中 XML 功能增强的更多信息，请参阅本期中 Bob Beauchemin 撰写的“XML in Yukon: New Version Showcases Native XML Type and Advanced Data Handling”一文。）www.iTbulo.com-ZO8xOW1

错误处理

SQL Server Yukon Beta 1 为在 T-SQL 中进行错误处理引入了新的 TRY/CATCH 构造。此构造可以用于捕获事务中止错误，甚至是在 SQL Server 以前的版本中会引起批处理中止的错误（转换错误、死锁等）。新的构造无法处理的错误类型是那些会导致会话中止的错误（通常是严重度为 21 和更高的错误，如硬件错误）。通常，您的错误处理代码如图 1 中所示。www.iTbulo.com-ZO8xOW1

XACT_ABORT 设置打开了，这样 SQL Server 可以将任何错误当作事务中止错误，从而使其能够被捕获和处理。在 TRY 块内，任何在显式事务内出现的错误会使控制权传递给紧跟在 TRY 块之后的 CATCH 块。如果没有错误出现，则跳过 CATCH 块。如果想研究所发生错误的类型并相应地做出反应，必须将 @@error 的返回值保存到位于 CATCH 块开始处的一个变量中，然后再开始研究。否则 @@error 返回的值可能不正确，因为除了 DECLARE 之外的任何语句都能够更改它。www.iTbulo.com-ZO8xOW1

当事务中止错误发生在位于 TRY 块里的事务内且控制权传递给 CATCH 块时，事务就进入了注定失败的状态。在您显式地发出一个 ROLLBACK 命令之前，锁是不会释放的，已经持续存储的工作也无法逆转。在发出 ROLLBACK 之前，不允许启动任何需要打开隐式或者显式事务的操作。您可以检查导致了错误的事务中已经更改的资源的内容，这样可以看到什么发生了更改，但是必须发出一个 ROLLBACK，以采取需要发生事务的补救措施。请注意，为了捕获 CATCH 块内出现的错误，必须在嵌套 TRY/CATCH 构造内编写代码。为了看一个更详细的示例，我们首先创建一个 ErrorLog 表（其中错误处理代码要对注释进行审核），然后创建 T1 和 T2 表，对它们发出查询，如我用图 2 的代码所完成的功能那样。www.iTbulo.com-ZO8xOW1

接下来，在新的连接（称为连接 1）中运行图 3 中的脚本（称为脚本 1）。脚本 1 将锁的超时设定设置为 30 秒并将死锁优先级设置为低，从那么它在一个死锁情况中自愿成为一个按正常优先级运行的进程发生死锁的牺牲品。TRY 块中的代码更新了 T1，等待 10 秒，然后从 T2 选择。如果事务无错误地完成，将在 ErrorLog 表中插入一行，其中有一个注释表明它成功完成。www.iTbulo.com-ZO8xOW1

CATCH 块设计成捕获主键冲突错误、锁的超时设定到期和重试逻辑的死锁错误。您可以通过更改赋给位于代码开始处的变量 @retry 的值，重新设置所需的重试次数，现在这个值被设置为 2。www.iTbulo.com-ZO8xOW1

在第一次运行图 3 中的代码之后，查看 ErrorLog 的内容。请注意事务成功完成了。要测试是否发生主键冲突错误，打开一个新的连接（称为连接 2）并运行以下代码：www.iTbulo.com-ZO8xOW1

INSERT INTO T1 VALUES(3)

回到连接 1 并再次运行脚本 1。如果查看 ErrorLog 的内容，应该可以看到其中记录了一个主键冲突错误。转到连接 2 并通过运行以下命令删除刚插入的行：www.iTbulo.com-ZO8xOW1

DELETE FROM T1 WHERE col1 = 3

要测试锁的超时设定是否到期，在连接 2 中运行以下代码：www.iTbulo.com-ZO8xOW1

BEGIN TRAN

UPDATE T1 SET col1 = 1

回到连接 1 并再次运行脚本 1。在大约 30 秒后，应该出现一个错误。查看 ErrorLog 的内容，可以发现记录了一条锁的超时设定到期。转到连接 2 并发出一条 ROLLBACK 命令以回滚事务。www.iTbulo.com-ZO8xOW1

为了测试是否存在死锁，到连接 2 并粘贴以下代码，但是暂时不运行：www.iTbulo.com-ZO8xOW1

DECLARE @i AS INT

BEGIN TRAN

SET @i = 1

WHILE @i <= 2

BEGIN

UPDATE T2 SET col1 = 2

WAITFOR DELAY '00:00:10'

SELECT * FROM T1

WAITFOR DELAY '00:00:05'

SET @i = @i + 1

END

ROLLBACK

转到连接 1，运行脚本 1 中的代码，然后立即运行连接 2 中的代码。大约一分钟之后，您将看到连接 1 中出现错误。查看 ErrorLog 的内容可以注意到在死锁错误之后进行了两次重试尝试，第三次尝试成功了，没有发生错误。查询 ErrorLog 表并查看其内容。www.iTbulo.com-ZO8xOW1

最后，如果想要在 TRY 块内引发您自己的事务中止错误，您可以使用 TRAN_ABORT 选项调用 RAISERROR 命令。www.iTbulo.com-ZO8xOW1

返回页首

SNAPSHOT 隔离

Yukon 引入了一种新的隔离级别，称为 SNAPSHOT，它允许您使用以下这种模式：写入程序不会阻碍读取程序，而且为读取程序提供了它们所请求数据的已提交版本。SQL Server Yukon 在 tempdb 中维护着一个链接列表，负责跟踪行的更改并为读取程序构造一个较旧的已提交的数据版本。这种隔离对于开放式锁定而言是有用的，在开放式锁定中 UPDATE 冲突并不常见。如果进程 1 检索数据，稍后又试图对它进行修改，如果进程 2 在进程 1 检索和修改之间也修改了同一数据，那么 SQL Server 就会因为出现冲突，在进程 1 试图进行修改时生成一个错误。然后，进程 1 可以尝试重新发出事务。这种模式在更新冲突不太常见的情况下会非常高效。www.iTbulo.com-ZO8xOW1

为了能够工作在 SNAPSHOT 隔离级别模式下，必须打开数据库选项 ALLOW_SNAPSHOT_ISOLATION，稍后您就会看到这一点。为了模拟一个写入程序不阻碍阅读程序的情景，则创建一个 testdb 数据库，打开相应的数据库选项，并通过运行以下代码，并创建一个在 datacol 列中具有值为“Version1”的 T1 表：www.iTbulo.com-ZO8xOW1

CREATE DATABASE testdb

GO

USE testdb

ALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ON

CREATE TABLE T1

(

keycol  INT         NOT NULL PRIMARY KEY,

datacol VARCHAR(10) NOT NULL

)

INSERT INTO T1 VALUES(1, 'Version1')

从连接 1 发出以下代码，它将打开一个事务并将 datacol 中的值更改为“Version2”：www.iTbulo.com-ZO8xOW1

USE testdb

BEGIN TRAN

UPDATE T1 SET datacol = 'Version2' WHERE keycol = 1

SELECT * FROM T1

转到连接 2 并运行以下代码，它将把会话的隔离级别设置为 SNAPSHOT，并检索 T1 的内容：www.iTbulo.com-ZO8xOW1

USE testdb

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT * FROM T1

请注意即使连接 2 将值改为“Version2”（但是还没有提交更改），检索回的还是“Version1”。www.iTbulo.com-ZO8xOW1

现在转到连接 1 并提交事务，然后用一个 COMMIT 命令关闭所有连接。为了尝试开放式锁定，打开两个新的连接，转到连接 1 并运行以下代码，它将把会话的隔离级别设置为 SNAPSHOT，打开一个事务，并从 T1 检索数据：www.iTbulo.com-ZO8xOW1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM T1

转到连接 2 并发出一条 UPDATE 命令：www.iTbulo.com-ZO8xOW1

UPDATE T1 SET datacol = 'Version3' WHERE keycol = 1

回到连接 1 并尝试更新前面检索到的同一数据。它已经被连接 2 修改了：www.iTbulo.com-ZO8xOW1

UPDATE T1 SET datacol = 'Version4' WHERE keycol = 1

将会获得错误，通知您 SQL Server 不能使用快照隔离访问数据库 testdb 中的表 T1，而且您应该重试事务。www.iTbulo.com-ZO8xOW1

返回页首

WAITFOR 命令

Yukon 中 WAITFOR 命令在许多方面进行了增强。除了等待指定的持续时间或者等待到某个 datetime 值，现在您还可以请求等待一条至少影响一行的 T-SQL 语句。可以指定命令等待以下语句之一：SELECT、INSERT、UPDATE、DELETE 或者 RECEIVE。前面的四个无需解释了；RECEIVE 指的是从队列中接收一条消息。如果希望在指定的毫秒数之后停止等待，可以选择性地指定一个超时设定值。WAITFOR 命令的语法如下：www.iTbulo.com-ZO8xOW1

WAITFOR(<statement>) [,TIMEOUT <timeout_value>]

Yukon 中另一个对 T-SQL 的功能增强允许您从数据操作语言 (DML) 的语句而不是 SELECT (INSERT, UPDATE, DELETE) 返回输出。一个新的 OUTPUT 子句允许您请求通过引用 INSERTED 和 DELETED 表所返回的列的新旧 image，与在触发器中引用它们的方式类似。甚至可以指定一条 INTO 子句并将输出导入到一个表变量中。另一处功能增强允许您通过修改语句指定 READPAST 提示，可以跳过已经锁定的行。www.iTbulo.com-ZO8xOW1

使用前面所述功能增强的一个示例是让几个进程等待一条 DELETE 语句从表删除至少一行，将输出导入到一个表变量中，每个进程都并行地处理数据的不同部分。为了看到这种情形，创建以下 MsgQueue 表：www.iTbulo.com-ZO8xOW1

USE tempdb

CREATE TABLE MsgQueue

(

msgid   INT         NOT NULL IDENTITY PRIMARY KEY,

msgdata VARCHAR(15) NOT NULL

)

打开一个或者更多连接，并在每个连接中运行以下代码，周期性地在表中插入新的消息：www.iTbulo.com-ZO8xOW1

SET NOCOUNT ON

USE tempdb

WHILE 1 = 1

BEGIN

INSERT INTO MsgQueue VALUES('Msg' +

CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR(10)))

WAITFOR DELAY '00:00:01'

END

接下来，您需要再打开几个其他的新连接，并在每个连接中运行图 4 中的代码，模拟对新到消息的处理。www.iTbulo.com-ZO8xOW1

返回页首

BULK 行集提供程序

Yukon 引入了新的 BULK 行集提供程序，这使您可以在 OPENROWSET 函数中指定以关系形式高效地访问文件。可以按类似于使用 BULK INSERT 语句的方式使用 BULK 提供程序，但是不用将输出发送给表。您必须指定一个格式文件，这与使用 bcp.exe 或者 BULK INSERT 语句时的格式文件相同。以下代码说明了如何使用格式文件 c:\temp\textfile1.fmt 访问称为 c:\temp\textfile1.txt 的文件，为结果表提供了别名 C，并为结果列提供了别名 col1、col2 和 col3：www.iTbulo.com-ZO8xOW1

SELECT col1, col2, col3

FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',

FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)

除了 FORMATFILE 选项以外，您还可以在 OPENROWSET 函数的括号中指定以下选项：CODEPAGE、DATAFILETYPE、FIELDTERMINATOR、FIRSTROW、LASTROW 和 ROWTERMINATOR。您还可以使用 INSERT SELECT 将数据高效地加载到一个表中并可以为加载选项指定表提示：www.iTbulo.com-ZO8xOW1

INSERT INTO MyTable WITH (BULK_CHECK_CONSTRAINTS)

SELECT col1, col2, col3

FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',

FORMATFILE = 'c:\temp\textfile1.fmt') AS C(col1, col2, col3)

其他可以指定为表提示的选项包括：BULK_BATCHSIZE、BULK_FIRE_TRIGGERS、BULK_KEEPIDENTITY、BULK_KEEPNULLS、BULK_KILOBYTES_PER_BATCH、BULK_MAXERRORS 和 ROWS_PER_BATCH。www.iTbulo.com-ZO8xOW1

使用 BULK 行集提供程序，您可以比以前使用常规 DML 更容易地将一个文件加载到表的列中。现在，对于大对象您不用再局限于 TEXT、NTEXT 和 IMAGE 数据类型，还可以使用 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX) 数据类型。新的 MAX 选项允许您用操作常规数据类型相同的方式操作大对象。例如，以下 UPDATE 语句将一个文本文件存储到一个定义为 VARCHAR(MAX) 的表列中：www.iTbulo.com-ZO8xOW1

UPDATE LOBs

SET clob_col =  (SELECT clob_data

FROM OPENROWSET(BULK 'c:\temp\textfile1.txt',

SINGLE_CLOB) AS C(clob_data))

WHERE keycol = 1

SINGLE_NCLOB 选项告诉 SQL Server，大对象是字符格式的。类似的，SINGLE_CLOB 指定了一个常规字符格式的大对象，而 SINGLE_BLOB 指定了二进制格式。返回的列的名称是 BulkColumn，但是正如前面的代码片段所说明的，您可以为其指定自己的别名。www.iTbulo.com-ZO8xOW1

返回页首

TOP 选项

Yukon 中的 T-SQL TOP 选项有两处显著的功能增强。现在您可以将一个表达式指定为 TOP 的参数，表达式可以包含变量甚至是独立的查询。您还可以通过改进型 DML (INSERT, UPDATE, DELETE) 使用 TOP 选项。www.iTbulo.com-ZO8xOW1

为了指定一个表达式，必须将它用括号括起来。当不使用 PERCENT 选项时，表达式应该是 BIGINT 数据类型的；当使用 PERCENT 选项时，应该是范围从 0 到 100 的一个浮点值。以下代码说明了如何使用带有一个变量的表达式，以按所请求数量返回 AdventureWorks 数据库中 SalesOrderHeader 的最早定单：www.iTbulo.com-ZO8xOW1

USE AdventureWorks

DECLARE @n AS BIGINT

SET @n = 5

SELECT TOP (@n) *

FROM SalesOrderHeader AS SOH

ORDER BY OrderDate, SalesOrderID

SalesOrderID 用作附加键。类似的，以下示例说明了如何使用 PERCENT 选项按所请求百分比返回最早定单：www.iTbulo.com-ZO8xOW1

DECLARE @p AS FLOAT

SET @p = 0.01

SELECT TOP (@p) PERCENT *

FROM SalesOrderHeader AS SOH

ORDER BY OrderDate, SalesOrderID

应得到 4 行结果，因为 SalesOrderHeader 表包含 31,519 行，而 31,519 x .0001 舍入之后等于 4。www.iTbulo.com-ZO8xOW1

通过改进型 DML 允许启用 TOP 的主要动机是替代 SET ROWCOUNT 选项，这一选项 SQL Server 无法很好地进行优化。SET ROWCOUNT 选项经常修改，以用来对大量的行进行批处理，防止事务日志爆满，以及避免单独的锁提升为完全的表锁。要想了解如何使用新的 TOP 功能成批地删除行，首先将 SalesOrderHeader 表的内容复制到 MySalesOrderHeader，并运行以下代码创建 OrderDate 和 SalesOrderID 列的索引：www.iTbulo.com-ZO8xOW1

SELECT *

INTO MySalesOrderHeader

FROM SalesOrderHeader

CREATE UNIQUE CLUSTERED INDEX idx_uc_OrderDate_SalesOrderID

ON MySalesOrderHeader(OrderDate, SalesOrderID)

要以 1,000 个为一批删除定单年份早于 2003 的所有行，使用以下代码：www.iTbulo.com-ZO8xOW1

WHILE 1 = 1

BEGIN

DELETE TOP (1000)

FROM MySalesOrderHeader WHERE OrderDate < '20030101'

IF @@rowcount < 1000 BREAK

END

SQL Server 对这样的代码所进行的优化，比使用 SET ROWCOUNT 选项要高效得多。现在，您可以不要 MySalesOrderHeader 表了：www.iTbulo.com-ZO8xOW1

DROP TABLE MySalesOrderHeader

返回页首

APPLY 运算符

APPLY 是在一个查询的 FROM 子句中指定的新的关系运算符。它允许您对外部表的每一行调用表值函数，可选地使用外部表的列作为函数的参数。APPLY 运算符有两种形式：CROSS APPLY 和 OUTER APPLY。如果表值函数为其返回一个空集合的话，前者不返回外部表的行，而后者则返回一个 NULL 值的行而不是函数的列。要使用 APPLY 运算符，首先创建以下 Arrays 表，它存储着多个逗号分隔的值数组：www.iTbulo.com-ZO8xOW1

CREATE TABLE Arrays

(

arrid INT NOT NULL IDENTITY PRIMARY KEY,

array VARCHAR(7999) NOT NULL

)

INSERT INTO Arrays VALUES(')

INSERT INTO Arrays VALUES('10')

INSERT INTO Arrays VALUES('20,40,30')

INSERT INTO Arrays VALUES('-1,-3,-5')

接下来，创建 fn_splitarr 表值函数，它接受一个数组作为参数并返回包含多个单独元素及其位置的一个表（参见图 5）。为了测试此函数，运行以下代码：www.iTbulo.com-ZO8xOW1

SELECT * FROM fn_splitarr('20,40,30')

输出应该如以下行所示：www.iTbulo.com-ZO8xOW1

pos         value

---         -----

1           20

2           40

3           30

现在使用 CROSS APPLY 运算符为 Arrays 中的每行调用函数：www.iTbulo.com-ZO8xOW1

SELECT A.arrid, F.*

FROM Arrays AS A

CROSS APPLY fn_splitarr(array) AS F

然后在您的输出中根据以下行检查值：www.iTbulo.com-ZO8xOW1

arrid       pos         value

-----       ---         -----

2           1           10

3           1           20

3           2           40

3           3           30

4           1           -1

4           2           -3

4           3           -5

请注意 Arrays 中 arrid 为 1 的行没有返回，因为函数为其返回一个空集合。要从 Arrays 返回所有行，使用 OUTER APPLY（不必考虑函数是否为它们返回行）。www.iTbulo.com-ZO8xOW1

支持 APPLY 运算符带来了一项附加的功能，就是您现在可以引用表值函数并指定外部表的列作为子查询中的参数。例如，以下代码返回所有元素的和小于或者等于 10 的 Arrays：www.iTbulo.com-ZO8xOW1

SELECT *

FROM Arrays

WHERE (SELECT SUM(value) FROM fn_splitarr(array)) <= 10

返回页首

通用表表达式和递归查询

通用表表达式 (CTE) 允许您编写只在查询期间持续存储的命名表表达式。它们的简单形式提供了视图和派生表的混合功能。与视图类似，CTE 可以在外部查询中被引用多次，而与派生表类似，它只在查询期间持续存储。采用更复杂的形式，您可以编写递归的 CTE，从而更加容易和高效地操作树和图。www.iTbulo.com-ZO8xOW1

定义一个 CTE 时，要使用一个 WITH 子句后面紧跟 CTE 的名称，并可选地在括号中提供一个结果列别名的列表。后面是 AS 子句和包含 CTE 查询表达式的括号。最后是提供一个引用 CTE 结果的外部查询。在 CTE 的查询表达式内，您可以按自己的意愿引用变量。www.iTbulo.com-ZO8xOW1

图 6 中的代码给出了一个简单示例，编写一个非递归的 CTE 返回每年客户的销售定单值。显然，不使用 CTE 您也可以获得同样的结果。但是设想一下：如果您还希望每一行都返回前一年的总值以及与本年的差值，那又会怎么样呢。如果您选择使用派生表，就必须在一个派生表中指定本年的查询，而在另一个中指定前一年的查询，并用外部查询联接二者。凭借 CTE，您可以编写一个查询返回每年的总值，并用外部查询引用它两次（参见图 7）。www.iTbulo.com-ZO8xOW1

但是 CTE 的真正强大之处是它们的递归形式。在 CTE 的括号内，您可以定义独立的或者向回引用 CTE 的查询。独立的查询（那些不引用 CTE 名称的查询）称为固定成员，只能调用一次。向回引用 CTE 名称的查询称为递归成员，可以重复调用，直到查询不再返回行。固定成员可以使用 UNION 或者 UNION ALL 运算符互相追加，具体取决于是否愿意消除重复项。而递归成员必须使用 UNION ALL 运算符追加。www.iTbulo.com-ZO8xOW1

举一个说明递归 CTE 用途的示例场景，考虑 AdventureWorks 数据库中的 BillOfMaterials 表。这个表代表一个典型的材料帐单，其中产品的组装形成了一个非循环的有向图。每个产品都是用其他产品组装的，而其他产品又是用另一些产品组装的，因此没有循环关系。这种组装产品包含的产品关系用 AssemblyID 和 ComponentID 列表示。PerAssemblyQty 包含 AssemblyID 所表示的每个产品的组件产品（用 ComponentID 表示）的数量。已经过时的关系在 ObsoleteDate 列中指定了一个日期。如果您只对非过时数据感兴趣，应该测试这个列是否为 NULL。表中还有其他有用的信息，包括度量单位，但是就我们要说明的意图而言，所有其他列都可以忽略。www.iTbulo.com-ZO8xOW1

图 8 中的代码生成了 ProductID 210 的分解图数据。图 9 给出了这种视图的一部分；描述了产品之间的包含关系。在 CTE 的主体内，第一个查询没有引用 CTE 的名称，因此它是一个固定成员，并且只能调用一次。请注意查询将查找组件 ID 为 210 而组装 ID 为 NULL 的行，这意味着它是一个顶层产品。查询确保此关系没有过时，并返回组件 ID 和数量。递归成员返回组装（通过在 CTE 的名称和 BillOfMaterials 表之间联接从前面的步骤返回）内包含的产品。第一次调用递归成员的时候，以前的步骤是固定成员返回的结果。第二次调用的时候，以前的步骤是第一次调用递归成员返回的结果，以此类推，直到递归成员返回一个空的集合。www.iTbulo.com-ZO8xOW1

递归成员通过用前一步骤的数量乘上组件的数量计算组件的累积数量。外部查询引用 CTE 的名称，获得对固定成员和递归成员所有调用的统一结果。外部查询将 CTE 与 Products 表联接，以获得产品名称，生成图 10 中的 90 行（有删节）。每个组件在输出中都可多次出现，例如产品 835，因为它可以参与不同的组装。可以修改外部查询按产品的 ID 和名称将结果分组，获得每个产品的总数量。代码如图 8 所示，而外部查询如下所示：www.iTbulo.com-ZO8xOW1

SELECT B.ProductID, P.Name,

SUM(B.Qty) AS TotalQty

FROM BOMCTE AS B

JOIN Product AS P

ON P.ProductID = B.ProductID

GROUP BY B.ProductID, P.Name

ORDER BY B.ProductID;

如果您怀疑其中存在循环，想要限制递归调用的数量，可以在外部查询之后马上指定 MAXRECURSION 选项：www.iTbulo.com-ZO8xOW1

WITH...

outer_query

OPTION(MAXRECURSION 30)

此选项将在 CTE 超过指定限制的时候，使 SQL Server 引发一个错误。如果没有指定这个选项，SQL Server 中的默认值是 100。如果不想有限制的话，必须指定 0。请注意您可以编写自定义代码检测循环关系，但是这超出了本文的范围。www.iTbulo.com-ZO8xOW1

返回页首

PIVOT 运算符

SQL Server Yukon 中新的 PIVOT 运算符允许您编写交叉表查询将行转为列。UNPIVOT 运算符则刚好相反 — 处理已旋转数据，将列转为行。图 11 给出了想要返回每个销售人员的年总销售定单值，且每年的值在不同列中显示时在数据库中使用 PIVOT 运算符的结果。www.iTbulo.com-ZO8xOW1

当使用 PIVOT 运算符时要注意的重要一点是，需要为它提供一个查询表达式，表达式使用视图、派生表或者 CTE 只返回所关注的列。原因在于，PIVOT 在幕后实际是对运算符没有显式引用的所有列进行一个隐式的 GROUP BY 操作。在这里，需要的是销售人员 ID、定单年份和定单值：www.iTbulo.com-ZO8xOW1

USE AdventureWorks

SELECT

SOH.SalesPersonID,

YEAR(SOH.OrderDate) AS OrderYear,

SOD.OrderQty * SOD.UnitPrice AS OrderValue

FROM SalesOrderHeader AS SOH

JOIN SalesOrderDetail AS SOD

ON SOD.SalesOrderID = SOH.SalesOrderID

SQL Server 会明白“GROUP BY”列的列表应该是输入表中没有被聚合函数或者 IN 子句里 PIVOT 运算符显式引用的列列表。因此如果您不想获取隐式 GROUP BY 列列表中不需要的列，需要为聚合函数、IN 子句和隐式 GROUP BY 给 PIVOT 运算符提供一个只包含所关注列的输入表。这可以通过使用一个 CTE 或者一个派生表（包含只返回所关注列的以前查询）实现。www.iTbulo.com-ZO8xOW1

图 12 中的代码说明了如何在 CTE 内使用这个查询，并让外部查询对 CTE 的结果发出一个 PIVOT 操作。SUM(OrderValue) 告诉 PIVOT 要填充已旋转列的单元格应该计算哪个聚合。FOR 子句告诉 PIVOT 哪个源列包含了旋转为结果列的值。IN 子句包含着要显示为结果列名称的值列表。www.iTbulo.com-ZO8xOW1

SQL Server 要求显式地在 IN 子句中指定要旋转为结果列的值列表。不能在使用静态查询的同时让 SQL Server 找出 OrderYear 中的所有不同值。为了达到这一目的，必须使用动态执行动态地构造查询字符串，如图 13 中的代码所示。www.iTbulo.com-ZO8xOW1

为了看到 UNPIVOT 运算符的作用，首先创建 SalesPivoted 表，这通过运行图 12 中的查询，在 FROM 子句之前加上“SELECT INTO SalesPivoted”实现（参见图 14）。UNPIVOT 运算符的参数与 PIVOT 的参数非常类似。但是这时需要指定结果列的名称，结果列将在一列中包含所有已旋转的单元格的值。在 FOR 子句之后，指定结果列的名称，该结果列存储已旋转列的名称作为列值。在 IN 子句后的括号中，指定想要取消旋转的已旋转列的列表：www.iTbulo.com-ZO8xOW1

SELECT *

FROM SalesPivoted

UNPIVOT(OrderValue

FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U

UNPIVOT 并不为包含 NULL 值的单元格返回行。为了清除数据库中我建立的多余的表和索引，运行以下代码：www.iTbulo.com-ZO8xOW1

DROP INDEX SalesOrderHeader.idx_nc_OrderDate

DROP TABLE SalesPivoted

返回页首

触发器和通知

SQL Server Yukon Beta 1 引入了对数据定义语言 (DDL) 触发器的支持，允许您捕获 DDL 操作并对其做出反应，可选地回滚操作。多个 DDL 触发器是同步工作的，紧跟在触发器事件之后，与以前版本的 SQL Server 中触发器工作方式类似。SQL Server 还支持一种可以使用通知的异步事件使用机制，允许您订阅以在某些事件发生的时候获得通知。www.iTbulo.com-ZO8xOW1

以下触发器是在数据库一级创建的，可以捕获 DROP TABLE 的尝试：www.iTbulo.com-ZO8xOW1

CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLE

AS

RAISERROR('Not allowed to drop tables.', 10, 1)

ROLLBACK

-- For debug

PRINT 'DROP TABLE attempt in database ' + DB_NAME() + '.'

PRINT EventData()

GO

您可以定义触发器来触发特定的 DDL 事件，如 CREATE_TABLE、DROP_TABLE、ALTER_TABLE、CREATE_VIEW，等等，或者如果想要触发器触发数据库中所有 DDL 事件，您也可以指定 DDL_DATABASE_LEVEL_EVENTS。在触发器内，可以调用 EventData 函数返回有关触发了触发器的进程和操作的信息。可以对函数返回的 XML 进行研究，并相应地做出反应。www.iTbulo.com-ZO8xOW1

为了测试触发器，首先创建表 TestDrop 并通过运行以下代码在其中插入一行：www.iTbulo.com-ZO8xOW1

CREATE TABLE TestDROP(col1 INT)

INSERT INTO TestDROP VALUES(1)

接下来，尝试除去表：www.iTbulo.com-ZO8xOW1

DROP TABLE TestDROP

DROP 尝试被捕获了，并输出了一条消息，指示不允许除去表。此外，EventData 函数的返回值用 XML 格式输出，以用于调试目的。（实际上，在触发器内您可以查看 XML 数据，它包含了许多有用的信息，可以从中确定什么样的操作最符合您的需要。例如，您可以防止在一天的特定时间里除去某些表。）触发器回滚操作，这样表就不会从数据库中除去。要除去触发器，需要发出以下代码语句：www.iTbulo.com-ZO8xOW1

DROP TRIGGER prevent_drop_table ON DATABASE

您还可以创建一个触发器捕获服务器级别的事件。例如，以下触发器就捕获了登录操作事件，如创建、更改或者除去一个登录：www.iTbulo.com-ZO8xOW1

CREATE TRIGGER audit_ddl_logins ON ALL SERVER

FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN

AS

PRINT 'DDL LOGIN took place.'

PRINT EventData()

GO

这里触发器只是输出一条通知，表明事件发生，并包含事件的细节。但是您当然可以研究事件细节并做出相应的反应。为了测试触发器，运行以下代码然后查看结果：www.iTbulo.com-ZO8xOW1

CREATE LOGIN login1 WITH PASSword = '123'

ALTER LOGIN login1 WITH PASSWORD = 'xyz'

DROP LOGIN login1

代码识别出 DDL 登录事件，而且事件数据是用 XML 格式生成的。如果愿意，您可以查看事件数据并审核感觉比较重要的信息。www.iTbulo.com-ZO8xOW1

如果想除去触发器，运行以下代码：www.iTbulo.com-ZO8xOW1

DROP TRIGGER audit_ddl_logins ON ALL SERVER

返回页首

小结

为 Yukon 提供的 T-SQL 功能增强和新功能允许您更高效地操作数据，更容易地开发应用程序，并提高了您的错误处理能力。处理数据操作时，T-SQL 仍然是 SQL Server 中最佳的开发选择，而且现在您拥有了更加丰富的开发环境。为了使您在体验这些新功能集合时更加轻松，本文中描述的所有示例都可以通过本文开始处的链接下载。www.iTbulo.com-ZO8xOW1

关键词：语法