SQL Server 2005 排序函數實現高效分頁：ROW_NUMBER() 取代臨時表

背景：SQL 2000 分頁的痛點

SQL Server 2000 缺乏內建排序函數，分頁常用「建立含 IDENTITY 欄位的暫存表 → 插入排序結果 → 依 IDENTITY 篩選頁面」模式，效能隨資料量線性下降，且佔用 tempdb 資源。

SQL 2005 突破：內建視窗函數

SQL Server 2005 引入 ANSI SQL:2003 標準視窗函數：

• ROW_NUMBER()：連續不重複排序號（最適合分頁）；
• RANK() / DENSE_RANK()：相同值同名次，前者跳號，後者不跳；
• NTILE(n)：分桶分析。

語法範例：為 Orders 表按 OrderID 遞減產生行號

SELECT ROW_NUMBER() OVER (ORDER BY OrderID DESC) AS RowNum, OrderID
FROM Orders
ORDER BY RowNum;

高效分頁預存程序實作 (SQL 2005+)

CREATE PROCEDURE usp_PagedQuery
    @PageIndex  INT,      -- 0-based 頁碼
    @PageSize   INT,      -- 每頁筆數
    @TotalRows  INT OUTPUT -- 總筆數
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 計算總筆數（可優化為快取或近似值）
    SELECT @TotalRows = COUNT(*) FROM Orders;
    
    -- 2. CTE + ROW_NUMBER() 實現分頁
    WITH PagedData AS (
        SELECT 
            OrderID, CustomerID, OrderDate, Freight,
            ROW_NUMBER() OVER (ORDER BY OrderID DESC) AS RowNum
        FROM Orders
    )
    SELECT OrderID, CustomerID, OrderDate, Freight
    FROM PagedData
    WHERE RowNum BETWEEN @PageIndex * @PageSize + 1 
                     AND (@PageIndex + 1) * @PageSize
    ORDER BY RowNum;
END
GO

-- 呼叫範例：第 2 頁 (0-based)，每頁 10 筆
DECLARE @Total INT
EXEC usp_PagedQuery 1, 10, @Total OUTPUT
SELECT @Total AS TotalRows

效能對比測試 (Orders 表 100 萬列，無覆蓋索引)

關鍵優化技巧

1. 覆蓋索引：CREATE INDEX IX_Orders_Paging ON Orders (OrderID DESC) INCLUDE (CustomerID, OrderDate, Freight) → 避免 Key Lookup，純索引掃描；
2. 總筆數快取：高併發場景將 COUNT(*) 移出預存程序，由背景作業定期更新快取表；
3. 避免 SELECT *：僅查詢必要欄位，減少排序緩衝區記憶體壓力；
4. 參數嗅探防護：OPTION (RECOMPILE) 或 OPTIMIZE FOR (@PageIndex=0) 避免首頁計畫套用深分頁。

遷移建議：SQL 2000 → 2005+ 分頁重構

1. 識別所有分頁預存程序/動態 SQL（搜尋 IDENTITY/TOP 巢狀/tempdb 表）；
2. 重寫為 ROW_NUMBER() CTE 模式；
3. 建立覆蓋索引支援排序欄位；
4. 壓力測試驗證深分頁效能（第 1000 頁等）；
5. 部署上線，移除舊版暫存表邏輯。

結語

ROW_NUMBER() OVER (ORDER BY ...) 是 SQL Server 2005 最具實用價值的 T-SQL 增強之一。它將分頁從「O(N) 寫入 TempDB + 索引掃描」降維為「O(log N) 索引定位 + 固定記憶體排序」，徹底解決深分頁效能崩潰問題。**所有 SQL 2005+ 專案的分頁查詢，應無條件採用視窗函數實作**。