萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

SQL Server 2005 實用新功能速覽:TOP 表達式、分頁、排名、TRY CATCH、CTE、Web Service 發布

前言

SQL Server 2005 對比 2000 版本改進巨大,以下六大新功能極大提升開發效率與程式碼可維護性,均以 Northwind 範例資料庫演示。

1. TOP 表達式支援變數

2000 版 TOP 僅接受常數,2005 允許變數:

DECLARE @n INT; SET @n = 10;
SELECT TOP(@n) * FROM Orders;

2. 原生分頁查詢 (ROW_NUMBER)

無需臨時表,單語句完成高效分頁:

SELECT * FROM (
    SELECT OrderId, Freight, ROW_NUMBER() OVER(ORDER BY Freight) AS row
    FROM Orders
) a WHERE row BETWEEN 20 AND 30;

3. 排名函數 (RANK)

SELECT * FROM (
    SELECT OrderId, Freight, RANK() OVER(ORDER BY Freight) AS rank
    FROM Orders
) a WHERE rank BETWEEN 20 AND 30;

4. 結構化異常處理 (TRY...CATCH)

告別逐行檢查 @@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;

5. 通用表達式 (CTE) 與遞迴查詢

替代臨時表,支援遞迴:

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;

6. 原生發布 Web Service (需 Windows 2003 SP1)

無需 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 更接近現代程式語言特性,大幅降低複雜業務邏輯的實現成本,建議升級專案優先評估採用。

copyright © 萬盛學電腦網 all rights reserved