功能強大的全新 T-SQL 文法極大地提升了 SQL Server 的可程式化性

發布日期： 11/15/2004 | 更新日期： 11/15/2004

Itzik Ben-Gan

本文是根據 Microsoft SQL Server 代號“Yukon”的 Beta 1 編寫的，此處含有的所有資訊都可能會更改。

下載本文的代碼：TSQLinYukon.exe (117KB)

註：本文是在產品投放生產之前編寫的，因此，我們無法保證此處包含的任何細節都與在交付使用的產品中發現的細節完全一致。文中資訊描述的是本文發布之時的產品，僅供規劃之用。這些資訊可在任何時候更改，恕不預先通知。

摘要 即將推出的 SQL Server 版本中的 T-SQL 語言將提供比之前的版本更強大的功能和靈活性。增加和增強功能包括使用 TRY/CATCH 構造進行錯誤處理、SNAPSHOT 隔離、和 WAITFOR 方面的增強。此外還比較重要的有 BULK 行集提供者、通用表運算式、遞迴查詢、PIVOT 和 UNPIVOT 運算子，等等。本文將介紹這些功能，以使讀者能夠為下一版本的 SQL Server 做好準備。

本頁內容

	錯誤處理
	SNAPSHOT 隔離
	WAITFOR 命令
	BULK 行集提供者
	TOP 選項
	APPLY 運算子
	通用表運算式和遞迴查詢
	PIVOT 運算子
	觸發器和通知
	小結

SQL Server 下一版本 — 代號“Yukon”的 Beta 1 版引入了許多對 T-SQL 的功能增強和新增功能，可以提高您的表達能力、錯誤管理水平和效能。本文中，我將討論一些重要的功能增強和新功能，包括錯誤處理、遞迴查詢和隔離。我將把注意力主要放在錯誤管理和相關的改進，並簡要描述其他方面的功能增強。

除了這裡敘述的功能以外，T-SQL 還有許多其他重要功能增強我沒有深入探討，因為其中一些從概念上講並不新，而另一些又需要單獨討論。這些方麵包括訊息處理和服務代理平台，分區和 XML 的功能增強。（有關 Yukon 中 XML 功能增強更多資訊，請參閱本期中 Bob Beauchemin 撰寫的“XML in Yukon: New Version Showcases Native XML Type and Advanced Data Handling”一文。）

錯誤處理

SQL Server Yukon Beta 1 為在 T-SQL 中進行錯誤處理引入了新的 TRY/CATCH 構造。此構造可以用於捕獲事務中止錯誤，甚至是在 SQL Server 以前的版本中會引起批處理中止的錯誤（轉換錯誤、死結等）。新的構造無法處理的錯誤類型是那些會導致會話中止的錯誤（通常是嚴重度為 21 和更高的錯誤，如硬體錯誤）。通常，您的錯誤處理代碼 1 中所示。

XACT_ABORT 設定開啟了，這樣 SQL Server 可以將任何錯誤當作事務中止錯誤，從而使其能夠被捕獲和處理。在 TRY 塊內，任何在明確交易內出現的錯誤會使控制權傳遞給緊跟在 TRY 塊之後的 CATCH 塊。如果沒有錯誤出現，則跳過 CATCH 塊。如果想研究所發生錯誤的類型並相應地做出反應，必須將 @@error 的傳回值儲存到位於 CATCH 塊開始處的一個變數中，然後再開始研究。否則 @@error 返回的值可能不正確，因為除了 DECLARE 之外的任何語句都能夠更改它。

當事務中止錯誤發生在位於 TRY 塊裡的事務內且控制權傳遞給 CATCH 塊時，事務就進入了註定失敗的狀態。在您顯式地發出一個 ROLLBACK 命令之前，鎖是不會釋放的，已經持續儲存的工作也無法逆轉。在發出 ROLLBACK 之前，不允許啟動任何需要開啟隱式或者明確交易的操作。您可以檢查導致了錯誤的事務中已經更改的資源的內容，這樣可以看到什麼發生了更改，但是必鬚髮出一個 ROLLBACK，以採取需要發生事務的補救措施。請注意，為了捕獲 CATCH 塊內出現的錯誤，必須在嵌套 TRY/CATCH 構造內編寫代碼。為了看一個更詳細的樣本，我們首先建立一個 ErrorLog 表（其中錯誤處理代碼要對注釋進行審核），然後建立 T1 和 T2 表，對它們發出查詢，如我用圖 2 的代碼所完成的功能那樣。

接下來，在新的串連（稱為串連 1）中運行圖 3 中的指令碼（稱為指令碼 1）。指令碼 1 將鎖的逾時設定設定為 30 秒並將死結優先順序設定為低，從那麼它在一個死結情況中自願成為一個按正常優先順序啟動並執行進程發生死結的犧牲品。TRY 塊中的代碼更新了 T1，等待 10 秒，然後從 T2 選擇。如果事務無錯誤地完成，將在 ErrorLog 表中插入一行，其中有一個注釋表明它成功完成。

CATCH 塊設計成捕獲主鍵衝突錯誤、鎖的逾時設定到期和重試邏輯的死結錯誤。您可以通過更改賦給位於代碼開始處的變數 @retry 的值，重新設定所需的重試次數，現在這個值被設定為 2。

在第一次運行圖 3 中的代碼之後，查看 ErrorLog 的內容。請注意事務成功完成了。要測試是否發生主鍵衝突錯誤，開啟一個新的串連（稱為串連 2）並運行以下代碼：

INSERT INTO T1 VALUES(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 命令以復原事務。

為了測試是否存在死結，到串連 2 並粘貼以下代碼，但是暫時不運行：

DECLARE @i AS INTBEGIN 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 + 1ENDROLLBACK

轉到串連 1，運行指令碼 1 中的代碼，然後立即運行串連 2 中的代碼。大約一分鐘之後，您將看到串連 1 中出現錯誤。查看 ErrorLog 的內容可以注意到在死結錯誤之後進行了兩次重試嘗試，第三次嘗試成功了，沒有發生錯誤。查詢 ErrorLog 表並查看其內容。

最後，如果想要在 TRY 塊內引發您自己的事務中止錯誤，您可以使用 TRAN_ABORT 選項調用 RAISERROR 命令。

返回頁首

SNAPSHOT 隔離

Yukon 引入了一種新的隔離等級，稱為 SNAPSHOT，它允許您使用以下這種模式：寫入程式不會阻礙讀取程式，而且為讀取程式提供了它們所請求資料的已提交版本。SQL Server Yukon 在 tempdb 中維護著一個連結清單，負責跟蹤行的更改並為讀取程式構造一個較舊的已提交的資料版本。這種隔離對於開放式鎖定而言是有用的，在開放式鎖定中 UPDATE 衝突並不常見。如果進程 1 檢索資料，稍後又試圖對它進行修改，如果進程 2 在進程 1 檢索和修改之間也修改了同一資料，那麼 SQL Server 就會因為出現衝突，在進程 1 試圖進行修改時產生一個錯誤。然後，進程 1 可以嘗試重新發出事務。這種模式在更新衝突不太常見的情況下會非常高效。

為了能夠工作在 SNAPSHOT 隔離等級模式下，必須開啟資料庫選項 ALLOW_SNAPSHOT_ISOLATION，稍後您就會看到這一點。為了類比一個寫入程式不阻礙閱讀程式的情景，則建立一個 testdb 資料庫，開啟相應的資料庫選項，並通過運行以下代碼，並建立一個在 datacol 列中具有值為“Version1”的 T1 表：

CREATE DATABASE testdbGOUSE testdbALTER DATABASE testdb SET ALLOW_SNAPSHOT_ISOLATION ONCREATE TABLE T1(  keycol  INT         NOT NULL PRIMARY KEY,  datacol VARCHAR(10) NOT NULL)INSERT INTO T1 VALUES(1, 'Version1')

從串連 1 發出以下代碼，它將開啟一個事務並將 datacol 中的值更改為“Version2”：

USE testdbBEGIN TRAN  UPDATE T1 SET datacol = 'Version2' WHERE keycol = 1  SELECT * FROM T1

轉到串連 2 並運行以下代碼，它將把會話的隔離等級設定為 SNAPSHOT，並檢索 T1 的內容：

USE testdbSET TRANSACTION ISOLATION LEVEL SNAPSHOTSELECT * FROM T1

請注意即使串連 2 將值改為“Version2”（但是還沒有提交更改），檢索回的還是“Version1”。

現在轉到串連 1 並提交事務，然後用一個 COMMIT 命令關閉所有串連。為了嘗試開放式鎖定，開啟兩個新的串連，轉到串連 1 並運行以下代碼，它將把會話的隔離等級設定為 SNAPSHOT，開啟一個事務，並從 T1 檢索資料：

SET TRANSACTION ISOLATION LEVEL SNAPSHOTBEGIN TRAN  SELECT * FROM T1

轉到串連 2 並發出一條 UPDATE 命令：

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

回到串連 1 並嘗試更新前面檢索到的同一資料。它已經被串連 2 修改了：

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

將會獲得錯誤，通知您 SQL Server 不能使用快照隔離訪問資料庫 testdb 中的表 T1，而且您應該重試事務。

返回頁首

WAITFOR 命令

Yukon 中 WAITFOR 命令在許多方面進行了增強。除了等待指定的期間或者等待到某個 datetime 值，現在您還可以請求等待一條至少影響一行的 T-SQL 陳述式。可以指定命令等待以下語句之一：SELECT、INSERT、UPDATE、DELETE 或者 RECEIVE。前面的四個無需解釋了；RECEIVE 指的是從隊列中接收一條訊息。如果希望在指定的毫秒數之後停止等待，可以選擇性地指定一個逾時設定值。WAITFOR 命令的文法如下：

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

Yukon 中另一個對 T-SQL 的功能增強允許您從資料操作語言 (DML) 的語句而不是 SELECT (INSERT, UPDATE, DELETE) 返回輸出。一個新的 OUTPUT 子句允許您請求通過引用 INSERTED 和 DELETED 表所返回的列的新舊 image，與在觸發器中引用它們的方式類似。甚至可以指定一條 INTO 子句並將輸出匯入到一個表變數中。另一處功能增強允許您通過修改語句指定 READPAST 提示，可以跳過已經鎖定的行。

使用前面所述功能增強一個樣本是讓幾個進程等待一條 DELETE 語句從表刪除至少一行，將輸出匯入到一個表變數中，每個進程都並行地處理資料的不同部分。為了看到這種情形，建立以下 MsgQueue 表：

USE tempdbCREATE TABLE MsgQueue(  msgid   INT         NOT NULL IDENTITY PRIMARY KEY,  msgdata VARCHAR(15) NOT NULL)

開啟一個或者更多串連，並在每個串連中運行以下代碼，周期性地在表中插入新的訊息：

SET NOCOUNT ONUSE tempdbWHILE 1 = 1BEGIN  INSERT INTO MsgQueue VALUES('Msg' +    CAST(CAST(RAND()*1000000000 AS INT) AS VARCHAR(10)))  WAITFOR DELAY '00:00:01'END

接下來，您需要再開啟幾個其他的新串連，並在每個串連中運行圖 4 中的代碼，類比對新到訊息的處理。

返回頁首

BULK 行集提供者

Yukon 引入了新的 BULK 行集提供者，這使您可以在 OPENROWSET 函數中指定以關係形式高效地訪問檔案。可以按類似於使用 BULK INSERT 語句的方式使用 BULK 提供者，但是不用將輸出發送給表。您必須指定一個格式檔案，這與使用 bcp.exe 或者 BULK INSERT 語句時的格式檔案相同。以下代碼說明了如何使用格式檔案 c:/temp/textfile1.fmt 訪問稱為 c:/temp/textfile1.txt 的檔案，為結果表提供了別名 C，並為結果列提供了別名 col1、col2 和 col3：

SELECT col1, col2, col3FROM 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。

使用 BULK 行集提供者，您可以比以前使用常規 DML 更容易地將一個檔案載入到表的列中。現在，對於大對象您不用再局限於 TEXT、NTEXT 和 IMAGE 資料類型，還可以使用 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX) 資料類型。新的 MAX 選項允許您用操作常規資料類型相同的方式操作大對象。例如，以下 UPDATE 語句將一個文字檔儲存到一個定義為 VARCHAR(MAX) 的表列中：

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，但是正如前面的程式碼片段所說明的，您可以為其指定自己的別名。

返回頁首

TOP 選項

Yukon 中的 T-SQL TOP 選項有兩處顯著的功能增強。現在您可以將一個運算式指定為 TOP 的參數，運算式可以包含變數甚至是獨立的查詢。您還可以通過改進型 DML (INSERT, UPDATE, DELETE) 使用 TOP 選項。

為了指定一個運算式，必須將它用括弧括起來。當不使用 PERCENT 選項時，運算式應該是 BIGINT 資料類型的；當使用 PERCENT 選項時，應該是範圍從 0 到 100 的一個浮點值。以下代碼說明了如何使用帶有一個變數的運算式，以按所請求數量返回 AdventureWorks 資料庫中 SalesOrderHeader 的最早定單：

USE AdventureWorksDECLARE @n AS BIGINTSET @n = 5SELECT TOP (@n) *FROM SalesOrderHeader AS SOHORDER BY OrderDate, SalesOrderID

SalesOrderID 用作附加鍵。類似的，以下樣本說明了如何使用 PERCENT 選項按所請求百分比返回最早定單：

DECLARE @p AS FLOATSET @p = 0.01SELECT TOP (@p) PERCENT *FROM SalesOrderHeader AS SOHORDER BY OrderDate, SalesOrderID

應得到 4 行結果，因為 SalesOrderHeader 表包含 31,519 行，而 31,519 x .0001 舍入之後等於 4。

通過改進型 DML 允許啟用 TOP 的主要動機是替代 SET ROWCOUNT 選項，這一選項 SQL Server 無法很好地進行最佳化。SET ROWCOUNT 選項經常修改，以用來對大量的行進行批處理，防止交易記錄爆滿，以及避免單獨的鎖提升為完全的表鎖。要想瞭解如何使用新的 TOP 功能成批地刪除行，首先將 SalesOrderHeader 表的內容複寫到 MySalesOrderHeader，並運行以下代碼建立 OrderDate 和 SalesOrderID 列的索引：

SELECT *INTO MySalesOrderHeaderFROM SalesOrderHeaderCREATE UNIQUE CLUSTERED INDEX idx_uc_OrderDate_SalesOrderID  ON MySalesOrderHeader(OrderDate, SalesOrderID)

要以 1,000 個為一批刪除定單年份早於 2003 的所有行，使用以下代碼：

WHILE 1 = 1BEGIN  DELETE TOP (1000)   FROM MySalesOrderHeader WHERE OrderDate < '20030101'  IF @@rowcount < 1000 BREAKEND

SQL Server 對這樣的代碼所進行的最佳化，比使用 SET ROWCOUNT 選項要高效得多。現在，您可以不要 MySalesOrderHeader 表了：

DROP TABLE MySalesOrderHeader

返回頁首

APPLY 運算子

APPLY 是在一個查詢的 FROM 子句中指定的新的關係運算子。它允許您對外部表格的每一行調用資料表值函式，可選地使用外部表格的列作為函數的參數。APPLY 運算子有兩種形式：CROSS APPLY 和 OUTER APPLY。如果資料表值函式為其返回一個空集合的話，前者不返回外部表格的行，而後者則返回一個 NULL 值的行而不是函數的列。要使用 APPLY 運算子，首先建立以下 Arrays 表，它儲存著多個逗號分隔的值數組：

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）。為了測試此函數，運行以下代碼：

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

輸出應該如以下行所示：

pos         value---         -----1           202           403           30

現在使用 CROSS APPLY 運算子為 Arrays 中的每行調用函數：

SELECT A.arrid, F.*FROM Arrays AS A  CROSS APPLY fn_splitarr(array) AS F

然後在您的輸出中根據以下行檢查值：

arrid       pos         value-----       ---         -----2           1           103           1           203           2           403           3           304           1           -14           2           -34           3           -5

請注意 Arrays 中 arrid 為 1 的行沒有返回，因為函數為其返回一個空集合。要從 Arrays 返回所有行，使用 OUTER APPLY（不必考慮函數是否為它們返回行）。

支援 APPLY 運算子帶來了一項附加的功能，就是您現在可以參考資料表值函數並指定外部表格的列作為子查詢中的參數。例如，以下代碼返回所有元素的和小於或者等於 10 的 Arrays：

SELECT *FROM ArraysWHERE (SELECT SUM(value) FROM fn_splitarr(array)) <= 10

返回頁首

通用表運算式和遞迴查詢

通用表運算式 (CTE) 允許您編寫只在查詢期間持續儲存的命名表運算式。它們的簡單形式提供了視圖和派生表的混合功能。與視圖類似，CTE 可以在外部查詢中被引用多次，而與派生表類似，它只在查詢期間持續儲存。採用更複雜的形式，您可以編寫遞迴的 CTE，從而更加容易和高效地操作樹和圖。

定義一個 CTE 時，要使用一個 WITH 子句後面緊跟 CTE 的名稱，並可選地在括弧中提供一個結果列別名的列表。後面是 AS 子句和包含 CTE 查詢運算式的括弧。最後是提供一個引用 CTE 結果的外部查詢。在 CTE 的查詢運算式內，您可以按自己的意願引用變數。

圖 6 中的代碼給出了一個簡單樣本，編寫一個非遞迴的 CTE 返回每年客戶的銷售定單值。顯然，不使用 CTE 您也可以獲得同樣的結果。但是設想一下：如果您還希望每一行都返回前一年的總值以及與本年的差值，那又會怎麼樣呢。如果您選擇使用派生表，就必須在一個派生表中指定本年的查詢，而在另一個中指定前一年的查詢，並用外部查詢聯結二者。憑藉 CTE，您可以編寫一個查詢返回每年的總值，並用外部查詢引用它兩次（參見圖 7）。

但是 CTE 的真正強大之處是它們的遞迴形式。在 CTE 的括弧內，您可以定義獨立的或者向回引用 CTE 的查詢。獨立的查詢（那些不引用 CTE 名稱的查詢）稱為固定成員，只能調用一次。向回引用 CTE 名稱的查詢稱為遞迴成員，可以重複調用，直到查詢不再返回行。固定成員可以使用 UNION 或者 UNION ALL 運算子互相追加，具體取決於是否願意消除重複項。而遞迴成員必須使用 UNION ALL 運算子追加。

舉一個說明遞迴 CTE 用途的樣本情境，考慮 AdventureWorks 資料庫中的 BillOfMaterials 表。這個表代表一個典型的材料帳單，其中產品的組裝形成了一個非迴圈的有向圖。每個產品都是用其他產品組裝的，而其他產品又是用另一些產品組裝的，因此沒有迴圈關係。這種組裝產品包含的產品關係用 AssemblyID 和 ComponentID 列表示。PerAssemblyQty 包含 AssemblyID 所表示的每個產品的組件產品（用 ComponentID 表示）的數量。已經過時的關係在 ObsoleteDate 列中指定了一個日期。如果您只對非過時資料感興趣，應該測試這個列是否為 NULL。表中還有其他有用的資訊，包括度量單位，但是就我們要說明的意圖而言，所有其他列都可以忽略。

圖 8 中的代碼產生了 ProductID 210 的分解圖資料。圖 9 給出了這種視圖的一部分；描述了產品之間的內含項目關聯性。在 CTE 的主體內，第一個查詢沒有引用 CTE 的名稱，因此它是一個固定成員，並且只能調用一次。請注意查詢將尋找組件 ID 為 210 而組裝 ID 為 NULL 的行，這意味著它是一個頂層產品。查詢確保此關係沒有過時，並返回組件 ID 和數量。遞迴成員返回組裝（通過在 CTE 的名稱和 BillOfMaterials 表之間聯結從前面的步驟返回）內包含的產品。第一次調用遞迴成員的時候，以前的步驟是固定成員返回的結果。第二次調用的時候，以前的步驟是第一次調用遞迴成員返回的結果，以此類推，直到遞迴成員返回一個空的集合。

遞迴成員通過用前一步驟的數量乘上組件的數量計算群組件的累積數量。外部查詢引用 CTE 的名稱，獲得對固定成員和遞迴成員所有調用的統一結果。外部查詢將 CTE 與 Products 表聯結，以獲得產品名稱，產生圖 10 中的 90 行（有刪節）。每個組件在輸出中都可多次出現，例如產品 835，因為它可以參與不同的組裝。可以修改外部查詢按產品的 ID 和名稱將結果分組，獲得每個產品的總數量。代碼 8 所示，而外部查詢如下所示：

SELECT B.ProductID, P.Name,    SUM(B.Qty) AS TotalQtyFROM BOMCTE AS B  JOIN Product AS P    ON P.ProductID = B.ProductIDGROUP BY B.ProductID, P.NameORDER BY B.ProductID;

如果您懷疑其中存在迴圈，想要限制遞迴調用的數量，可以在外部查詢之後馬上指定 MAXRECURSION 選項：

WITH...outer_queryOPTION(MAXRECURSION 30)

此選項將在 CTE 超過指定限制的時候，使 SQL Server 引發一個錯誤。如果沒有指定這個選項，SQL Server 中的預設值是 100。如果不想有限制的話，必須指定 0。請注意您可以編寫自訂代碼檢測迴圈關係，但是這超出了本文的範圍。

返回頁首

PIVOT 運算子

SQL Server Yukon 中新的 PIVOT 運算子允許您編寫交叉資料表查詢將行轉為列。UNPIVOT 運算子則剛好相反 — 處理已旋轉資料，將列轉為行。圖 11 給出了想要返回每個銷售人員的年總銷售定單值，且每年的值在不同列中顯示時在資料庫中使用 PIVOT 運算子的結果。

當使用 PIVOT 運算子時要注意的重要一點是，需要為它提供一個查詢運算式，運算式使用視圖、派生表或者 CTE 只返回所關注的列。原因在於，PIVOT 在幕後實際是對運算子沒有顯式引用的所有列進行一個隱式的 GROUP BY 操作。在這裡，需要的是銷售人員 ID、定單年份和定單值：

USE AdventureWorksSELECT  SOH.SalesPersonID,   YEAR(SOH.OrderDate) AS OrderYear,  SOD.OrderQty * SOD.UnitPrice AS OrderValueFROM 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 或者一個派生表（包含只返回所關注列的以前查詢）實現。

圖 12 中的代碼說明了如何在 CTE 內使用這個查詢，並讓外部查詢對 CTE 的結果發出一個 PIVOT 操作。SUM(OrderValue) 告訴 PIVOT 要填充已旋轉列的儲存格應該計算哪個彙總。FOR 子句告訴 PIVOT 哪個源列包含了旋轉為結果列的值。IN 子句包含著要顯示為結果列名稱的值列表。

SQL Server 要求顯式地在 IN 子句中指定要旋轉為結果列的值列表。不能在使用靜態查詢的同時讓 SQL Server 找出 OrderYear 中的所有不同值。為了達到這一目的，必須使用動態執行動態地構造查詢字串， 13 中的代碼所示。

為了看到 UNPIVOT 運算子的作用，首先建立 SalesPivoted 表，這通過運行圖 12 中的查詢，在 FROM 子句之前加上“SELECT INTO SalesPivoted”實現（參見圖 14）。UNPIVOT 運算子的參數與 PIVOT 的參數非常類似。但是這時需要指定結果列的名稱，結果列將在一列中包含所有已旋轉的儲存格的值。在 FOR 子句之後，指定結果列的名稱，該結果列儲存已旋轉列的名稱作為列值。在 IN 子句後的括弧中，指定想要取消旋轉的已旋轉列的列表：

SELECT *FROM SalesPivoted  UNPIVOT(OrderValue     FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U

UNPIVOT 並不為包含 NULL 值的儲存格返回行。為了清除資料庫中我建立的多餘的表和索引，運行以下代碼：

DROP INDEX SalesOrderHeader.idx_nc_OrderDateDROP TABLE SalesPivoted

返回頁首

觸發器和通知

SQL Server Yukon Beta 1 引入了對資料定義語言 (Data Definition Language) (DDL) 觸發器的支援，允許您捕獲 DDL 操作並對其做出反應，可選地復原操作。多個 DDL 觸發程序是同步工作的，緊跟在觸發事件之後，與以前版本的 SQL Server 中觸發器工作方式類似。SQL Server 還支援一種可以使用通知的非同步事件使用機制，允許您訂閱以在某些事件發生的時候獲得通知。

以下觸發器是在資料庫一級建立的，可以捕獲 DROP TABLE 的嘗試：

CREATE TRIGGER prevent_drop_table ON DATABASE FOR DROP_TABLEASRAISERROR('Not allowed to drop tables.', 10, 1)ROLLBACK-- For debugPRINT '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 進行研究，並相應地做出反應。

為了測試觸發器，首先建立表 TestDrop 並通過運行以下代碼在其中插入一行：

CREATE TABLE TestDROP(col1 INT)INSERT INTO TestDROP VALUES(1)

接下來，嘗試除去表：

DROP TABLE TestDROP

DROP 嘗試被捕獲了，並輸出了一條訊息，指示不允許除去表。此外，EventData 函數的傳回值用 XML 格式輸出，以用於調試目的。（實際上，在觸發器內您可以查看 XML 資料，它包含了許多有用的資訊，可以從中確定什麼樣的操作最符合您的需要。例如，您可以防止在一天的特定時間裡除去某些表。）觸發器復原操作，這樣表就不會從資料庫中除去。要除去觸發器，需要發出以下代碼語句：

DROP TRIGGER prevent_drop_table ON DATABASE

您還可以建立一個觸發器捕獲伺服器層級的事件。例如，以下觸發器就捕獲了登入操作事件，如建立、更改或者除去一個登入：

CREATE TRIGGER audit_ddl_logins ON ALL SERVER  FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGINASPRINT '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

返回頁首

小結

為 Yukon 提供的 T-SQL 功能增強和新功能允許您更高效地操作資料，更容易地開發應用程式，並提高了您的錯誤處理能力。處理資料操作時，T-SQL 仍然是 SQL Server 中最佳的開發選擇，而且現在您擁有了更加豐富的開發環境。為了使您在體驗這些新功能集合時更加輕鬆，本文中描述的所有樣本都可以通過本文開始處的連結下載。