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

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

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

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

錯誤處理

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

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

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

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

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

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

INSERT INTO T1 VALUES(3)

回到連接 1 並再次運行腳本 1。如果查看 ErrorLog 的內容，應該可以看到其中記錄了一個主鍵沖突錯誤。轉到連接 2 並通過運行以下命令刪除剛插入的行：www.iTbulo.com-ZO8xOW1

DELETE FROM T1 WHERE col1 = 3

要測試鎖的超時設定是否到期，在連接 2 中運行以下代碼：www.iTbulo.com-ZO8xOW1

BEGIN TRAN

UPDATE T1 SET col1 = 1

回到連接 1 並再次運行腳本 1。在大約 30 秒後，應該出現一個錯誤。查看 ErrorLog 的內容，可以發現記錄了一條鎖的超時設定到期。轉到連接 2 並發出一條 ROLLBACK 命令以回滾事務。www.iTbulo.com-ZO8xOW1

為了測試是否存在死鎖，到連接 2 並粘貼以下代碼，但是暫時不運行：www.iTbulo.com-ZO8xOW1

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 表並查看其內容。www.iTbulo.com-ZO8xOW1

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

返回頁首

SNAPSHOT 隔離

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

為了能夠工作在 SNAPSHOT 隔離級別模式下，必須打開數據庫選項 ALLOW_SNAPSHOT_ISOLATION，稍後您就會看到這一點。為了模擬一個寫入程序不阻礙閱讀程序的情景，則創建一個 testdb 數據庫，打開相應的數據庫選項，並通過運行以下代碼，並創建一個在 datacol 列中具有值為“Version1”的 T1 表：www.iTbulo.com-ZO8xOW1

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”：www.iTbulo.com-ZO8xOW1

USE testdb

BEGIN TRAN

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

SELECT * FROM T1

轉到連接 2 並運行以下代碼，它將把會話的隔離級別設置為 SNAPSHOT，並檢索 T1 的內容：www.iTbulo.com-ZO8xOW1

USE testdb

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT * FROM T1

請注意即使連接 2 將值改為“Version2”（但是還沒有提交更改），檢索回的還是“Version1”。www.iTbulo.com-ZO8xOW1

現在轉到連接 1 並提交事務，然後用一個 COMMIT 命令關閉所有連接。為了嘗試開放式鎖定，打開兩個新的連接，轉到連接 1 並運行以下代碼，它將把會話的隔離級別設置為 SNAPSHOT，打開一個事務，並從 T1 檢索數據：www.iTbulo.com-ZO8xOW1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN

SELECT * FROM T1

轉到連接 2 並發出一條 UPDATE 命令：www.iTbulo.com-ZO8xOW1

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

回到連接 1 並嘗試更新前面檢索到的同一數據。它已經被連接 2 修改了：www.iTbulo.com-ZO8xOW1

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

將會獲得錯誤，通知您 SQL Server 不能使用快照隔離訪問數據庫 testdb 中的表 T1，而且您應該重試事務。www.iTbulo.com-ZO8xOW1

返回頁首

WAITFOR 命令

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

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

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

使用前面所述功能增強的一個示例是讓幾個進程等待一條 DELETE 語句從表刪除至少一行，將輸出導入到一個表變量中，每個進程都並行地處理數據的不同部分。為了看到這種情形，創建以下 MsgQueue 表：www.iTbulo.com-ZO8xOW1

USE tempdb

CREATE TABLE MsgQueue

(

msgid   INT         NOT NULL IDENTITY PRIMARY KEY,

msgdata VARCHAR(15) NOT NULL

)

打開一個或者更多連接，並在每個連接中運行以下代碼，周期性地在表中插入新的消息：www.iTbulo.com-ZO8xOW1

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

接下來，您需要再打開幾個其他的新連接，並在每個連接中運行圖 4 中的代碼，模擬對新到消息的處理。www.iTbulo.com-ZO8xOW1

返回頁首

BULK 行集提供程序

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

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 將數據高效地加載到一個表中並可以為加載選項指定表提示：www.iTbulo.com-ZO8xOW1

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。www.iTbulo.com-ZO8xOW1

使用 BULK 行集提供程序，您可以比以前使用常規 DML 更容易地將一個文件加載到表的列中。現在，對於大對象您不用再局限於 TEXT、NTEXT 和 IMAGE 數據類型，還可以使用 VARCHAR(MAX)、NVARCHAR(MAX) 和 VARBINARY(MAX) 數據類型。新的 MAX 選項允許您用操作常規數據類型相同的方式操作大對象。例如，以下 UPDATE 語句將一個文本文件存儲到一個定義為 VARCHAR(MAX) 的表列中：www.iTbulo.com-ZO8xOW1

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

返回頁首

TOP 選項

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

為了指定一個表達式，必須將它用括號括起來。當不使用 PERCENT 選項時，表達式應該是 BIGINT 數據類型的；當使用 PERCENT 選項時，應該是范圍從 0 到 100 的一個浮點值。以下代碼說明了如何使用帶有一個變量的表達式，以按所請求數量返回 AdventureWorks 數據庫中 SalesOrderHeader 的最早定單：www.iTbulo.com-ZO8xOW1

USE AdventureWorks

DECLARE @n AS BIGINT

SET @n = 5

SELECT TOP (@n) *

FROM SalesOrderHeader AS SOH

ORDER BY OrderDate, SalesOrderID

SalesOrderID 用作附加鍵。類似的，以下示例說明了如何使用 PERCENT 選項按所請求百分比返回最早定單：www.iTbulo.com-ZO8xOW1

DECLARE @p AS FLOAT

SET @p = 0.01

SELECT TOP (@p) PERCENT *

FROM SalesOrderHeader AS SOH

ORDER BY OrderDate, SalesOrderID

應得到 4 行結果，因為 SalesOrderHeader 表包含 31,519 行，而 31,519 x .0001 捨入之後等於 4。www.iTbulo.com-ZO8xOW1

通過改進型 DML 允許啟用 TOP 的主要動機是替代 SET ROWCOUNT 選項，這一選項 SQL Server 無法很好地進行優化。SET ROWCOUNT 選項經常修改，以用來對大量的行進行批處理，防止事務日志爆滿，以及避免單獨的鎖提升為完全的表鎖。要想了解如何使用新的 TOP 功能成批地刪除行，首先將 SalesOrderHeader 表的內容復制到 MySalesOrderHeader，並運行以下代碼創建 OrderDate 和 SalesOrderID 列的索引：www.iTbulo.com-ZO8xOW1

SELECT *

INTO MySalesOrderHeader

FROM SalesOrderHeader

CREATE UNIQUE CLUSTERED INDEX idx_uc_OrderDate_SalesOrderID

ON MySalesOrderHeader(OrderDate, SalesOrderID)

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

WHILE 1 = 1

BEGIN

DELETE TOP (1000)

FROM MySalesOrderHeader WHERE OrderDate < '20030101'

IF @@rowcount < 1000 BREAK

END

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

DROP TABLE MySalesOrderHeader

返回頁首

APPLY 運算符

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

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

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

輸出應該如以下行所示：www.iTbulo.com-ZO8xOW1

pos         value

---         -----

1           20

2           40

3           30

現在使用 CROSS APPLY 運算符為 Arrays 中的每行調用函數：www.iTbulo.com-ZO8xOW1

SELECT A.arrid, F.*

FROM Arrays AS A

CROSS APPLY fn_splitarr(array) AS F

然後在您的輸出中根據以下行檢查值：www.iTbulo.com-ZO8xOW1

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（不必考慮函數是否為它們返回行）。www.iTbulo.com-ZO8xOW1

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

SELECT *

FROM Arrays

WHERE (SELECT SUM(value) FROM fn_splitarr(array)) <= 10

返回頁首

通用表表達式和遞歸查詢

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

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

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

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

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

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

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

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 選項：www.iTbulo.com-ZO8xOW1

WITH...

outer_query

OPTION(MAXRECURSION 30)

此選項將在 CTE 超過指定限制的時候，使 SQL Server 引發一個錯誤。如果沒有指定這個選項，SQL Server 中的默認值是 100。如果不想有限制的話，必須指定 0。請注意您可以編寫自定義代碼檢測循環關系，但是這超出了本文的范圍。www.iTbulo.com-ZO8xOW1

返回頁首

PIVOT 運算符

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

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

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 列列表中不需要的列，需要為聚合函數、IN 子句和隱式 GROUP BY 給 PIVOT 運算符提供一個只包含所關注列的輸入表。這可以通過使用一個 CTE 或者一個派生表（包含只返回所關注列的以前查詢）實現。www.iTbulo.com-ZO8xOW1

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

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

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

SELECT *

FROM SalesPivoted

UNPIVOT(OrderValue

FOR OrderYear IN([2001], [2002], [2003], [2004])) AS U

UNPIVOT 並不為包含 NULL 值的單元格返回行。為了清除數據庫中我建立的多余的表和索引，運行以下代碼：www.iTbulo.com-ZO8xOW1

DROP INDEX SalesOrderHeader.idx_nc_OrderDate

DROP TABLE SalesPivoted

返回頁首

觸發器和通知

SQL Server Yukon Beta 1 引入了對數據定義語言 (DDL) 觸發器的支持，允許您捕獲 DDL 操作並對其做出反應，可選地回滾操作。多個 DDL 觸發器是同步工作的，緊跟在觸發器事件之後，與以前版本的 SQL Server 中觸發器工作方式類似。SQL Server 還支持一種可以使用通知的異步事件使用機制，允許您訂閱以在某些事件發生的時候獲得通知。www.iTbulo.com-ZO8xOW1

以下觸發器是在數據庫一級創建的，可以捕獲 DROP TABLE 的嘗試：www.iTbulo.com-ZO8xOW1

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_EVENTS。在觸發器內，可以調用 EventData 函數返回有關觸發了觸發器的進程和操作的信息。可以對函數返回的 XML 進行研究，並相應地做出反應。www.iTbulo.com-ZO8xOW1

為了測試觸發器，首先創建表 TestDrop 並通過運行以下代碼在其中插入一行：www.iTbulo.com-ZO8xOW1

CREATE TABLE TestDROP(col1 INT)

INSERT INTO TestDROP VALUES(1)

接下來，嘗試除去表：www.iTbulo.com-ZO8xOW1

DROP TABLE TestDROP

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

DROP TRIGGER prevent_drop_table ON DATABASE

您還可以創建一個觸發器捕獲服務器級別的事件。例如，以下觸發器就捕獲了登錄操作事件，如創建、更改或者除去一個登錄：www.iTbulo.com-ZO8xOW1

CREATE TRIGGER audit_ddl_logins ON ALL SERVER

FOR CREATE_LOGIN, ALTER_LOGIN, DROP_LOGIN

AS

PRINT 'DDL LOGIN took place.'

PRINT EventData()

GO

這裡觸發器只是輸出一條通知，表明事件發生，並包含事件的細節。但是您當然可以研究事件細節並做出相應的反應。為了測試觸發器，運行以下代碼然後查看結果：www.iTbulo.com-ZO8xOW1

CREATE LOGIN login1 WITH PASSword = '123'

ALTER LOGIN login1 WITH PASSWORD = 'xyz'

DROP LOGIN login1

代碼識別出 DDL 登錄事件，而且事件數據是用 XML 格式生成的。如果願意，您可以查看事件數據並審核感覺比較重要的信息。www.iTbulo.com-ZO8xOW1

如果想除去觸發器，運行以下代碼：www.iTbulo.com-ZO8xOW1

DROP TRIGGER audit_ddl_logins ON ALL SERVER

返回頁首

小結

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

關鍵詞：語法