ttable.

其他影响 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 表变量中存储消息属性。