SQL Server 2005 對比 2000 版本改進巨大,以下六大新功能極大提升開發效率與程式碼可維護性,均以 Northwind 範例資料庫演示。
2000 版 TOP 僅接受常數,2005 允許變數:
DECLARE @n INT; SET @n = 10; SELECT TOP(@n) * FROM Orders;
無需臨時表,單語句完成高效分頁:
SELECT * FROM (
SELECT OrderId, Freight, ROW_NUMBER() OVER(ORDER BY Freight) AS row
FROM Orders
) a WHERE row BETWEEN 20 AND 30;
SELECT * FROM (
SELECT OrderId, Freight, RANK() OVER(ORDER BY Freight) AS rank
FROM Orders
) a WHERE rank BETWEEN 20 AND 30;
告別逐行檢查 @@ERROR,支援交易自動回滾:
SET XACT_ABORT ON;
BEGIN TRY
BEGIN TRAN;
INSERT INTO Orders(CustomerId) VALUES(-1); -- 故意違反 FK
COMMIT TRAN;
PRINT 'commited';
END TRY
BEGIN CATCH
ROLLBACK;
PRINT 'rolled back';
END CATCH;
替代臨時表,支援遞迴:
WITH OrderFreight AS (
SELECT OrderId, Freight, ROW_NUMBER() OVER(ORDER BY Freight) AS row
FROM Orders
)
SELECT OrderId, Freight FROM OrderFreight WHERE row BETWEEN 10 AND 20;
無需 IIS 與 .NET,直接透過 HTTP.SYS 發布存儲過程為 Web Service:
CREATE ENDPOINT Orders_Endpoint
STATE = STARTED
AS HTTP (
PATH = '/sql/orders',
AUTHENTICATION = (INTEGRATED),
PORTS = (CLEAR)
)
FOR SOAP (
WEBMETHOD 'CustOrdersOrders' (NAME = 'Northwind.dbo.CustOrdersOrders'),
WSDL = DEFAULT,
DATABASE = 'Northwind',
NAMESPACE = 'https://mysite.org/'
);
瀏覽 https://localhost/sql/orders?wsdl 即可獲取 WSDL。
這些功能讓 T-SQL 更接近現代程式語言特性,大幅降低複雜業務邏輯的實現成本,建議升級專案優先評估採用。