分类： C/C++
2008-04-23 21:45:05
Yukon 中的 T-SQL：T-SQL强大的新语法给予 SQL Server 进一步的可编程性
原著：
译者：
原文出处：
下载关于此文章的代码： (117KB)
本文基于 Microsoft 代号“Yukon”的SQL Server Beta 1版本，这里包含的所有信息都可能改变。
注：本文是在产品发布前写出来的，所以，我们不保证此文包含的任何细节与正式售卖品完全一致。本文付诸印刷时，所有描述该产品的信息仅能应用于计划目的。有关信息在任何时候都有可能改变，恕不事先通知。
摘要
即将发布的SQL Server版本里的T-SQL语言将比前一版本更强大和更灵活。其增加和增强部分包括通过TRY/CATCH结构进行错误处理、SNAPSHOT隔离和WAITFOR增强。同样重要的有：BULK行集提供者、通用表表达式、递归查询、PIVOT和UNPIVOT算子，及更多。本文介绍了这些特性，以便读者对SQL Server的下一版本做好准备。
SQL Server™的下一版本，代号为“Yukon”，其Beta 1版本的发布引入了一组T-SQL增强功能和新特性，这些可改进你的表示能力、错误处理和性能。本文中我将论述一些重要的增强功能和新特性，其包括错误处理、递归查询和隔离。我重点论叙错误处理和相关的改进，并简要描述其他增强功能。
除了这里描述的特征外，T-SQL有很多我未能深入讲述的其他重要增强；因为其中一些从概念上将并不是新的另一些则值得独立的论述。这些包括消息和Service Broker平台、分区和XML增强。更多关于Yukon中XML增强请参阅本期Bob Beauchemin所写的"Yukon 中的 XML：新版本展示原始 XML 类型和高级数据处理" .
错误处理
SQL Server Yukon Beta1版本T-SQL中引入了新的便于错误处理TRY/CATCH结构。这种结构允许你捕获事务异常中断错误，甚至那些在SQL Server上一版本中导致批处理终止的错误（转换错误、死锁等等）。新结构不能捕获的错误类型是那些导致会话终止的错误（通常是严重等级21及以上的错误，例如硬件错误）。典型地，你的错误处理代码看起来象Figure 1所示。
开启XACT_ABORT设置后，SQL Server将对任意错误视为事务异常中断错误，从而允许它被捕获并处理。TRY块内，发生在显示事务中任何错误导致控制转移到CATCH块，它紧跟着TRY块。如果没有错误发生，CATCH块被跳过。如果想检查被抛出的错误类型并相应地作处理，必须在CATCH块开始处将@@error的返回值保存到变量中，然后再开始检查它。另外，通过@@error返回的值可能不正确，因为包括DECLARE在内的任意表达式都可能会改变它。
当在位于TRY块内的一事务中一个事务异常中断错误发生后，控制权就被转移到CATCH块，事务进入毁灭（doomed）状态。直到显示执行ROLLBACK命令，系统才会释放锁，持续工作才会被回滚。直到执行ROLLBACK，你才会被允许发起任何需要开启一个隐式或显示事务的活动。能检查在发生错误的事务里被改变的资源内容，通过这可了解资源变化，然而，要执行需开启事务的任何纠正措施，你必须执行ROLLBACK。注意，为了捕获发生在CATCH块中的错误，必须使用嵌套TRY/CATCH结构编写代码。看一个更详细的例子，首先创建ErrorLog表，在此表中错误处理代码审计（audit）注解，然后创建T1和T2表，并对它们执行查询，如我在Figure 2用代码所写的。
接着，在一新连接（称其为连接1）中运行Figure 3中的脚本（称其为脚本1）。脚本1设置锁超时时间为30秒,死锁优先级为低，此脚本在正常优先级运行进程中的死锁场景里志愿成为死锁牺牲品。TRY块中的代码UPDATE表T1，等待10秒，然后从表T2中SELECT。如果事务无错误完成，一行数据会被插入ErrorLog表，其注释说明它成功完成。
CATCH块被设计用于通过重试逻辑来捕获主键违规错误、锁超时错误和死锁错误。可在代码开始处通过改变对变量@retry的值来设置所需的重试次数；它当前设置为2。
第一次运行Figure 3处代码后，检查ErrorLog表内容。注意事务已经成功完成。要测试一个主键违规错误，请打开一新连接（称其为连接2）并运行如下代码：
INSERT INTO T1 VALUE(3)返回连接1，再次运行脚本1。如果检查表ErrorLog内容，会看到一个主键违规错误已被记录。转到连接2，运行下面命令以删除刚插入的行：
DELETE FROM T1 WHERE col1 = 3要测试锁超时错误，在连接2中运行下面代码：
BEGIN TRAN UPDATE T1 SET col1 = 1返回连接1，再次运行脚本1。大约30秒后，你会得到一个错误。检查ErrorLog内容，会发现一个锁超时错误已被记录。转到连接2，执行ROLLBACK命令回滚事务。
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表，检查它的内容。
SNAPSHOT隔离
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”：
USE testdb BEGIN TRAN UPDATE T1 SET datacol = ''''Version2'''' WHERE keycol = 1 SELECT * FROM T1转到连接2，运行下列代码，它设置会话隔离级为SNAPSHOT并取回T1的内容：
USE testdb SET TRANSACTION ISOLATION LEVEL SNAPSHOT SELECT * FROM T1注意你取回的是“Version1”，即使连接2已将它改变为“Version2”（但是还没有提交改变）。
SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT * FROM T1转到连接2，执行UPDATE：
UPDATE T1 SET datacol = ''''Version3'''' WHERE keycol = 1返回连接1，设法update你以前获取的同样数据，这些数据已在连接2中被修改：
UPDATE T1 SET datacol = ''''Version4'''' WHERE keycol = 1你应该得到一个错误，它通知你SQL Server不能使用SNAPSHOT隔离级去存取数据库testdb的表T1和应重试你的事务。
WAITFOR命令
WAITFOR(Yukon中另一增强允许返回从数据操纵语言(INSERT，UPDATE，DELETE）而不只是SELECT语句的输出。新的OUTPUT子句允许请求列的旧或新数据通过引用INSERTED和DELETED表被返回，类似在触发器中引用它们。你甚至能指定INTO子句并定向其输出到一个表变量。另一增强是允许在修改语句里指定READPAST提示，这样就可跳过锁定行。) [,TIMEOUT ]
USE tempdb CREATE TABLE MsgQueue ( msgid INT NOT NULL IDENTITY PRIMARY KEY, msgdata VARCHAR(15) NOT NULL )打开一个或多个连接，在每个连接中运行下列代码以定时插入新消息到表中：
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
接着需打开数个其他新连接，并在每个连接中运行Figure 4中代码以模拟对新到达消息的处理。
BULK行集提供者（BULK Rowset Provider）
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来有效地载入数据到表中和为装载选项指定表提示：
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。
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指定大对象为规则字符格式，SINGEL_BLOB为二进制格式。返回列的名称为BulkColumn，然而如上一代码片段所示，可以为它指定自己的列名。
TOP选项
USE AdventureWorks DECLARE @n AS BIGINT SET @n = 5 SELECT TOP (@n) * FROM SalesOrderHeader AS SOH ORDER BY OrderDate, SalesOrderID排序时同种情况下SalesOrderID优先。类似地，下面例子示范如何使用PERCENT选项返回最先满足请求百分比的订单：
DECLARE @p AS FLOAT SET @p = 0.01 SELECT TOP (@p) PERCENT * FROM SalesOrderHeader AS SOH ORDER BY OrderDate, SalesOrderID如果SalesOrderHeader表包含31519行，31519×0.0001结果向上取整为4， 你会得到4行结果。
SELECT * INTO MySalesOrderHeader FROM SalesOrderHeader CREATE UNIQUE CLUSTERED INDEX idx_uc_OrderDate_SalesOrderID ON MySalesOrderHeader(OrderDate, SalesOrderID)要以批处理量为1000的方式删除所有订单年份比2003年早的行，用下列代码：
WHILE 1 = 1 BEGIN DELETE TOP (1000) FROM MySalesOrderHeader WHERE OrderDate < ""20030101"" IF @@rowcount < 1000 BREAK ENDSQL Server比使用SET ROWCOUNT更有效优化此代码。现在可以删除MySalesOrderHeader表：
DROP TABLE MySalesOrderHeader
APPLY算子
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表值函数，它接受一队列为参数并返回一个包含个体元素和他们的位置的表（见Figure 5）。要测试函数，运行此代码：
SELECT * FROM fn_splitarr(''''20,40,30'''')
输出应与下面相似：
pos value --- ----- 1 20 2 40 3 30现在，用CROSS ARRAY算子对表Arrays中每行调用函数：
SELECT A.arrid, F.* FROM Arrays AS A CROSS APPLY fn_splitarr(array) AS F然后对照下面检查输出：
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。
SELECT * FROM Arrays WHERE (SELECT SUM(value) FROM fn_splitarr(array)) <= 10
通用表表达式（Common Table Expressions ）和递归查询
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选项：
WITH... outer_query OPTION(MAXRECURSION 30)当CTE超过指定限制，此选项使SQL Server抛出错误。当没有指定此选项时，SQL Server默认设置为100。如果不想由限制，必须指定其为0。注意可编写检查循环关系的自定义代码，但这超出本文的范围。
PIVOT算子
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列清单中得到不需要的列，你需要为PIVOT算子提供一个输入表，它仅包含对合计函数、IN子句和隐式GROUP BY感兴趣的列。这可以通过使用CTE或导出表获取，其包含返回只感兴趣列的上一查询。
SELECT *
FROM SalesPivoted
UNPIVOT(OrderValue
FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U
UNPIVOT不会返回含有NULL值单元的行。要清空刚才在数据库中已建立的表和索引，运行下列代码：
DROP INDEX SalesOrderHeader.idx_nc_OrderDate DROP TABLE SalesPivoted
触发器和通知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_EVENTSXUAN。触发器内，可调用EventData函数以返回启动触发器的有关进程和活动的信息。可检查此函数返回的XML并作相应处理。
CREATE TABLE TestDROP(col1 INT) INSERT INTO TestDROP VALUES(1)接着，尝试删除表：
DROP TABLE TestDROPDROP企图被捕获，一条信息被打印以指示不允许删除此表。同样，为调试所需，EventData函数的返回值以XML格式被打印出。（实际上，触发器内可检查XML数据，它包含很多有用信息，以决定哪个过程行为最适合你需要。例如，可防止一天中某时刻删除某表。）触发器回滚活动，因而表没有从数据库中删除。要删除触发器，执行下列语句：
DROP TRIGGER prevent_drop_table ON DATABASE也可以创建捕获服务级别事件的触发器。例如，下面触发器捕获登录操作事件，如创建、改变或删除一次登录。
CREATE TRIGGER audit_ddl_logins ON ALL SERVER FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN AS PRINT ''''DDL LOGIN took place.'''' PRINT EventData() GO此例中，当事件发生后触发器仅仅打印通告信息和事件的详细信息。但是，当然，可检查事件详细内容并作相应的处理。要测试触发器，运行下列代码，然后看看结果：
CREATE LOGIN login1 WITH PASSWORD = ''''123'''' ALTER LOGIN login1 WITH PASSWORD = ''''xyz'''' DROP LOGIN login1一DDL登录事件被识别，然后事件数据以XML格式产生。如果你愿意，可检查事件数据和审查对你重要的信息。
DROP TRIGGER audit_ddl_logins ON ALL SERVER
总结 作者简介