SQL Server 2005 T-SQL的增強功能 [ZT]
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...等等。