SQL Server ROW_NUMBER() 通用分頁儲存程序：現代高效實作

適用場景

中小型資料表（< 10 萬筆）、頻繁分頁查詢、需快速開發維護。大數據量、高效能要求請個別優化（鍵值分頁、延遲關聯、索引下推）。

儲存程序設計

CREATE PROCEDURE dbo.Common_SelectWithPage
    @Sql NVARCHAR(MAX),          -- 含 ROW_NUMBER() 的完整查詢
    @CurrentPageNo INT,          -- 當前頁碼 (1-based)
    @PageSize INT,               -- 每頁筆數
    @TotalNum INT OUTPUT         -- 總筆數 (-1 表示需計算)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @SqlCmd NVARCHAR(MAX)

    -- 分頁查詢
    SET @SqlCmd = N'SELECT * FROM (' + @Sql + N') A 
        WHERE RowIndex BETWEEN ' + CAST((@CurrentPageNo - 1) * @PageSize + 1 AS NVARCHAR) + N' 
        AND ' + CAST(@CurrentPageNo * @PageSize AS NVARCHAR)
    EXEC sp_executesql @SqlCmd

    -- 計算總筆數（僅首次或需求時）
    IF @TotalNum = -1
    BEGIN
        DECLARE @CountSql NVARCHAR(MAX) = N'SELECT @Total = COUNT(*) FROM (' + @Sql + N') A'
        EXEC sp_executesql @CountSql, N'@Total INT OUTPUT', @TotalNum OUTPUT
    END
END

關鍵改良點

• 使用 sp_executesql 參數化 → 計畫快取重用、防注入
• NVARCHAR(MAX) 支援長查詢
• OUTPUT 參數回傳總筆數，避免額外結果集
• 條件計算總筆數（-1 觸發），減少重複統計

呼叫範例

DECLARE @Sql NVARCHAR(MAX) = N'
    SELECT *, ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowIndex
    FROM Orders WITH (NOLOCK)
    WHERE Status = 1'

DECLARE @TotalNum INT = -1
EXEC dbo.Common_SelectWithPage @Sql, 3, 20, @TotalNum OUTPUT
SELECT @TotalNum AS TotalRecords

ROW_NUMBER() 分頁原理

1. 內層查詢賦予每列唯一遞增序號
2. 外層以 BETWEEN 篩選頁面範圍
3. 優化器可將 TOP 推入內層（SQL 2012+），避免全排序

效能優化關鍵

現代替代方案：OFFSET/FETCH (SQL 2012+)

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE Status = 1
ORDER BY OrderDate DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY

優點：語法標準、單查詢、優化器原生支援、參數化友善。
缺點：無法直接取得總筆數（需 COUNT(*) OVER() 窗口函數或第二查詢）。

總筆數高效取得

-- 窗口函數法（單查詢，SQL 2012+）
SELECT *, COUNT(*) OVER() AS TotalCount
FROM (
    SELECT OrderID, CustomerID, OrderDate,
           ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowIndex
    FROM Orders WHERE Status = 1
) t
WHERE RowIndex BETWEEN 41 AND 60

或應用層快取總筆數（分頁不變時），定期重算。

遷移建議

• 新專案：直接用 OFFSET/FETCH + 窗口函數總計數
• 舊系統：保留儲存程序，改用 sp_executesql 參數化
• 極大資料：鍵值分頁 WHERE Id > @LastId ORDER BY Id OFFSET 0 ROWS FETCH NEXT 20