SQL Server 2005 T-SQL的增強功能

 

 

1. 增加Richer Data Type，例如varchar(max)、nvarchar(max)和varbinary(max)等資料類型，使用max指定最大2GB的儲存空間，可用於改進(取代)過去text, ntext和image無法跨頁儲存(8KB)的限制。另一個值得一提的是XML資料類型，用於儲存XML字串片段和XML檔案內容。

2. 支援try/catch error handling，用於異常攔截及異常處理，防止批次處理因異常而終止，但不包含嚴重等級在21以上的嚴重錯誤，例如違反資料完整一致性，或者硬體的錯誤等等。
try/catch結構如下：
--開啟xact_abort (預設為關閉)
set xact_abort on
begin try
begin tran
-- 批次處理
commit tran
end try
begin catch
rollback tran
--異常處理，例如print error_number(), error_saverity(), error_message()
end catch

3. Snapshot Isolation，Snapshot isolation是SQL Server 2005的一個新的隔離層級。它利用tempdb的linked list追蹤資料的異動，維持一份committed版本的資料，用於資料讀取，也就是將寫入程序與讀取程序作適當的隔離，使得寫入程序不會影響讀取程序，以提高資料的可用性。為了使用snapshot isolation，首先必須啟動database的snapshot，同時將connection的isolation level設定成snapshot，具體做法如下：
a. alter database mydb set allow_snapshot_isolation on
b. set transaction isolation level snapshot

4. Top enhance，SQL Server 2005允許使用數字表達式和子查詢指定查詢筆數或百分比，同時也可以用在Insert、Update和Delete等DML，比起set rowcount具有更佳的效能。例如取出單價最高前十名的商品排行，可以寫成
declare @t
set @t=10
select top(@t) from products order by unitprice desc

5. Output
SQL Server 2005增加了一個output關鍵字，用於Insert、Update和Delete等DML敘述，將傳回資料輸出到inserted或deleted資料表變數。Output許法如下：
output DML敘述 into @table_variable
執行Insert時，新增的資料會輸出到inserted資料表；執行Delete時，刪除的資料會輸出到deleted資料表；執行Update時，修改前的資料會輸出到deleted資料表，修改後的資料會輸出到inserted資料表。若在異動的過程中，需要讀取原始的資料進行邏輯運算，除了可以利用trigger之外，您也可以使用output達到相同的目的。

例如刪除單價低於100元以下的商品資料，並將這些商品的編號及名稱輸出到一個資料表變數中，則可以寫成
declare @table_var as table (c1 int, c2 varchar(10))
delete from products where unitpirce < 100
output deleted.productid, deleted.productname into @table_var

select * from @table_var

6. Ranking functions
SQL Server 2005加入了row_count、rank和dense_rank等3個新的排序函數，您可以使用這些函數更快更方便地找到所需的資料。
(1)row_count用於表示目前是第幾筆紀錄，第一筆紀錄為1，第二筆紀錄為2，依此類推。例如取出所有的商品資料，同時顯示紀錄編號，可以寫成
select row_count() over(order by unitprice) as rownum, * from products

(2)rank與row_count的主要不同在於若排序欄位的值相同，則賦予相同的紀錄編號，反之不相同，則維持原有的紀錄編號。例如取出10記錄，除了第1, 2, 3筆，以及第4, 5筆紀錄的排序欄位值相同，其餘皆不同，則這些紀錄的rank值分別依序為1, 1, 1, 4, 4, 6, 7, 8, 9, 10。

(3)dense_rank與row_count的主要不同在於若排序欄位的值相同，則賦予相同的紀錄編號，反之不相同，則將前一筆紀錄編號加一產生新的紀錄編號。如前例，這10筆紀錄的dense_rank值分別依序為1, 1, 1, 2, 2, 3, 4, 5, 6, 7，也就是第1, 2, 3筆紀錄並列第一，第4, 5筆紀錄並列第二，保持一組連續不斷號的dense_rank值。

7. CTE (Common Table Express) CTE是利用Select敘述所產生一個暫存的紀錄集，可用於外部重複參考或自我參考找出從屬關係的遞迴查詢等。
a. 定義CTE
CTE
With Products_CTE(ID, No)As
(Select ProductID, ProductNo From Products)
其中With之後為CTE名稱，As之後為CTE的主體，用於產生暫存的紀錄集。

b.外部參考CTE，使用CTE名稱參考暫存的紀錄集。
Select * From Products_CTE

SQL Server 2005的 T-SQL 語言比前個版本提供了更強大的功能和靈活性。例如上述介紹的TRY/CATCH結構進行錯誤處理、SNAPSHOT 隔離和CTE，此外還有Waitfor BULK、遞迴查詢、PIVOT、UNPIVOT與APPLY、DDL Trigger...等等。