在SQL Server应用中使用OUTPUT子句

 现在大多数描述SQL Server 2005新特性的文章都关注于华而不实的特性，如SQLCLR或XML数据类型，而对许多很好的老的T-SQL语言的改进没有得到应有的关注。我曾经从许多DBA口中听到令他们更兴奋的是看到T-SQL语言的改进,而不是那些新出现和发布的功能。对于SQL语言的一个很有用的实际改进是OUTPUT子句，它允许查询一个数据修改命令所影响的记录行。

　　本文将讨论OUTPUT子句在SQL Server中的具体应用。我会向你介绍如何很容易地使用OUTPUT子句实现审计和数据修改存档等业务需求，以及其它相关的内容。

 

　　OUTPUT子句的基本原理是很简单的——它返回由每个INSERT、UPDATE或DELETE命令所影响的记录行。OUTPUT可以在客户端应用程序中返回这些记录行，然后将它们插入到一个持久的或临时的表中，也可以将记录插入到一个表变量中。它的用法就是直接将OUTPUT子句附到任何一个INSERT/UPDATE/DELETE语句后。

 

　　OUTPUT子句中可以引用INSERTED或DELETED虚拟表，这取决于是否想要在数据修改前(DELETED表)或修改后(INSERTED表)得到数据。这跟使用触发器去修改数据的操作是很相似的。

 

　　注意：不能在一个INSERT语句中引用DELETED，也不能在一个DELETED语句中引用INSERTED，因为这些虚拟表在这两种情况下逻辑上是没有意义的，所以SQL Server不会去创建。

 

　　既然我们已经了解了OUTPUT子句在SQL Server中的基本用法，下面让我们看一些它的例子和SQL Server中的实际应用。我将从创建一个简单的Employee表开始：

 

CREATE TABLE dbo.Employee 　　( 　　EmployeeID INT NOT NULL IDENTITY(1, 1), 　　FirstName VARCHAR(50) NOT NULL, 　　LastName VARCHAR(50) NOT NULL, 　　Status VARCHAR(20) DEFAULT 'Single' 　　)

 

　　下一步，我们要插入一行数据并加上OUTPUT来返回执行插入操作时所插入到应用中的记录：

 

INSERT INTO dbo.Employee( FirstName, LastName ) 　　OUTPUT INSERTED.* 　　SELECT 'Susan', 'Kelley'

 

EmployeeID	FirstName	LastName	Status
1	Susan	Kelley	Single

 

　　我们可以看到，SQL Server返回INSERT语句所插入的记录。这个技术对于查找服务器生成的值并返回给应用程序是很有用的，如标记字段或字段默认值。

下一步，我们将来自INSERT语句的输出实时地插入到一个表中。例如，Susan结婚后变更了她的姓。这时，我们需要更新她的员工信息。公司政策规定我们必须保留所有员工的历史数据，因此我们需要存档旧的员工数据。这样我们就创建了一个表Employee_Archive及一些附加域：

CREATE TABLE dbo.Employee_Archive 　　( 　　EmployeeID INT NOT NULL IDENTITY(1, 1), 　　FirstName VARCHAR(50) NOT NULL, 　　LastName VARCHAR(50) NOT NULL, 　　[Status] VARCHAR(20) DEFAULT 'Single' 　　ChangedBy VARCHAR(300) NOT NULL, 　　ChangedDatetime DATETIME NOT NULL 　　)

　　现在我们可以更新Susan的信息记录了，同时使用OUTPUT子句和DELETED虚拟表将旧的记录行插入到Employee_Archive表中：

 

UPDATE dbo.Employee 　　SET LastName = 'Jones', 　　Status = 'Married' 　　OUTPUT DELETED.*, system_user, getdate() 　　INTO dbo.Employee_Archive 　　WHERE EmployeeID = 1

　　在执行这个查询后，我们会在Employee表中得到这些数据：

 

EmployeeID	FirstName	LastName	Status
1	Susan	Jones	Married

 

　　Employee_Archive表包含了一个有旧数据值、用户名和数据修改时间的记录行。这是我的计算机上的输出，你电脑上的输出可能有些不一样：

 

EmployeeID	FirstName	LastName	Status	ChangedBY	ChangedDatetime
1	Susan	Kelley	Single	rrehak	2008-04-21 02:04:18.310

 

　　另一个OUTPUT子句的实践用法是保存一系列受影响的记录以备后续处理。这在更新一组记录的时候是很有用的，在做一些额外的处理后再重新更新相同的记录集——我在使用一组INSERT/UPDATE/DELETE语句同步2个不同数据库时经常这样做。因为我们有了一列ID，我们就不必再次执行第一次的查询。相反，我们可以在WHERE子句中使用这些ID，从而避免再做一次复杂和大开销的查询去取回相同的数据集。下面的例子创建了一个临时表并将修改的员工记录的ID保存起来：

 

CREATE TABLE #EmployeeIDs 　　( 　　EmployeeID INT NOT NULL 　　) 　　GO 　　UPDATE dbo.Employees 　　SET LastName = LastName 　　OUTPUT INSERTED.EmployeeID 　　INTO #EmployeeIDs

 

　　在UPDATE语句执行后，临时表包含了ID和所有修改的记录。

 

　　如果需要从一个表清除大量的数据并转存到一个存档表中，OUTPUT子句是能够有效地节省处理时间的。大多数有经验的DBA都会将删除操作拆分成一组更小的删除操作，可能是100,000个或更多。所以真正的删除数据的代码可能会是这样的：

 

WHILE 1 = 1 　　BEGIN 　　BEGIN TRANSACTION 　　INSERT INTO ArchiveTable 　　SELECT * 　　FROM MainTable 　　WHERE ID BETWEEN @MinID AND @MaxID 　　DELETE FROM MainTable 　　WHERE ID BETWEEN @MinID AND @MaxID 　　COMMIT TRANSACTION 　　END

　　如果使用OUTPUT子句，我们就能够在一条语句中完成这个操作，并且可以节省很多处理时间，因为配置记录行只需要进行一次：

 

WHILE 1 = 1 　　BEGIN 　　DELETE FROM MainTable 　　OUTPUT DELETED.* 　　INTO ArchiveTable 　　WHERE ID BETWEEN @MinID AND @MaxID 　　END

　　我们可以看到，使用OUTPUT子句来简化代码和替代触发器有很多种可能的用法。在本文中，我介绍了如何使用OUTPUT语句去审计和存档数据、获取一组被修改的记录行和简化数据删除程序。