在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語句去審計和存檔資料、擷取一組被修改的記錄行和簡化資料刪除程式。