SQL Server 2005 實用新功能賞析：TOP 運算式、分頁、排名、TRY-CATCH、CTE、原生 Web Service

前言

SQL Server 2005 對比 2000 改進巨大，本文精選六個最實用、最能提升開發效率的新功能，配合 Northwind 範例資料庫示範。

1. TOP 運算式支援變數/子查詢

DECLARE @n INT = 10;
SELECT TOP (@n) * FROM Orders;  -- 2000 僅支援常數

應用場景：動態分頁、參數化 Top N 報表。

2. ROW_NUMBER() 完美解決分頁難題

-- 依 Freight 遞增，取第 20-30 筆
SELECT * FROM (
    SELECT OrderID, Freight, 
           ROW_NUMBER() OVER (ORDER BY Freight) AS rn
    FROM Orders
) a
WHERE rn BETWEEN 20 AND 30;

• 單一陳述式完成，效能優於 2000 臨時表法
• 配合 OVER (PARTITION BY ... ORDER BY ...) 可做群組內分頁

3. RANK() / DENSE_RANK() 彈性排名

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

• RANK()：並列同名次，下一名次跳號 (1,1,3)
• DENSE_RANK()：並列同名次，下一名次不跳 (1,1,2)
• NTILE(n)：將資料分成 n 桶，標示桶號

4. TRY...CATCH 結構化例外處理

SET XACT_ABORT ON;  -- 確保嚴重錯誤自動回滾
BEGIN TRY
    BEGIN TRAN;
    INSERT INTO Orders (CustomerID) VALUES (-1);  -- 故意違反 FK
    COMMIT TRAN;
    PRINT 'committed';
END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    PRINT 'rolled back: ' + ERROR_MESSAGE();
END CATCH

• 結束逐行檢查 @@ERROR 的時代
• ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_LINE() 等函數豐富錯誤資訊

5. CTE (Common Table Expression) 通用資料表運算式

基本用法：替代臨時表/巢狀子查詢

WITH OrderFreight AS (
    SELECT OrderID, Freight, 
           ROW_NUMBER() OVER (ORDER BY Freight) AS rn
    FROM Orders
)
SELECT OrderID, Freight 
FROM OrderFreight 
WHERE rn BETWEEN 10 AND 20;

進階：遞迴 CTE 處理階層資料

WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, Title, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Title, Level + 1
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart ORDER BY Level, EmployeeID;

遞迴 CTE 讓 SQL 輕鬆處理組織架構、BOM 物料清單、遞迴分類等樹狀結構。

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

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/'
);

• 無需 IIS、無需 .NET Framework、無需 ASP.NET
• 直接透過 HTTP.SYS 核心模式驅動程式暴露 SOAP 端點
• 瀏覽 https://localhost/sql/orders?wsdl 即可取得 WSDL
• 注意：此功能在 SQL Server 2008 已棄用，建議改用 WCF / Web API

總結對照表