SQL Server 百萬級分頁存儲過程：ROW_NUMBER() 方案實戰與效能優化

面對百萬級數據分頁，傳統 TOP + NOT IN、TOP + MAX(ID)、游標方案均存在深分頁劇烈變慢、計劃緩存膨脹等問題。本文提供基於 ROW_NUMBER() OVER() 的通用分頁存儲過程，經實測百萬表單頁 20 行穩定在 50-100ms 內。

存儲過程完整代碼

CREATE PROCEDURE dbo.P_GridViewPager
    @RecordTotal INT OUTPUT,      -- 總記錄數輸出
    @ViewName VARCHAR(800),       -- 表/視圖名
    @FieldName VARCHAR(800) = '*',-- 查詢列，* 或逗號分隔列表
    @KeyName VARCHAR(200) = 'Id', -- 排序/分頁鍵（建議唯一、有索引）
    @PageSize INT = 20,           -- 每頁行數
    @PageNo INT = 1,              -- 當前頁碼（1-based）
    @OrderString VARCHAR(200),    -- ORDER BY 子句內容，如 'Id DESC'
    @WhereString VARCHAR(800) = '1=1' -- WHERE 子句內容
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @BeginRow INT, @EndRow INT, @LimitSql VARCHAR(200);
    DECLARE @CountSql NVARCHAR(1000), @MainSql NVARCHAR(1000);

    -- 計算分頁行號範圍
    SET @BeginRow = (@PageNo - 1) * @PageSize + 1;
    SET @EndRow   = @PageNo * @PageSize;
    SET @LimitSql = 'Rows BETWEEN ' + CAST(@BeginRow AS VARCHAR) + ' AND ' + CAST(@EndRow AS VARCHAR);

    -- 1. 統計總行數（輸出參數）
    SET @CountSql = N'SELECT @RecordTotal = COUNT(*) 
                      FROM (SELECT ' + @KeyName + ' FROM ' + @ViewName + ' WHERE ' + @WhereString + ') AS T';
    EXEC sp_executesql @CountSql, N'@RecordTotal INT OUTPUT', @RecordTotal OUTPUT;

    -- 2. 主查詢：ROW_NUMBER 分頁
    SET @MainSql = N'SELECT * FROM (
                         SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderString + ') AS Rows, ' + @FieldName + '
                         FROM ' + @ViewName + ' WHERE ' + @WhereString + '
                     ) AS M WHERE ' + @LimitSql;

    EXEC (@MainSql);  -- 動態 SQL 不支援輸出參數，直接 EXEC 返回結果集
END;
GO

調用範例

DECLARE @Total INT;
EXEC dbo.P_GridViewPager 
    @RecordTotal = @Total OUTPUT,
    @ViewName = 'dbo.Orders',
    @FieldName = 'OrderId, CustomerId, OrderDate, TotalAmount',
    @KeyName = 'OrderId',
    @PageSize = 20,
    @PageNo = 3,
    @OrderString = 'OrderDate DESC',
    @WhereString = 'CustomerId = ''C001''';
SELECT @Total AS TotalRows;

核心優化點解析

深分頁優化：延遲關聯

若 @FieldName 包含大字段（VARCHAR(MAX)、XML），分頁內層僅選鍵值，外層再關聯回表：

SET @MainSql = N'SELECT T.* FROM (
                     SELECT ' + @KeyName + ' FROM (
                         SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderString + ') AS Rows, ' + @KeyName + '
                         FROM ' + @ViewName + ' WHERE ' + @WhereString + '
                     ) AS M WHERE ' + @LimitSql + '
                 ) AS P
                 INNER JOIN ' + @ViewName + ' T ON T.' + @KeyName + ' = P.' + @KeyName;'

適用場景與限制

• ✅ 百萬~億級表、OLTP 分頁、報表後端 API。
• ✅ 排序列有索引、且基數較高（唯一最佳）。
• ❌ 極高併發寫入場景（ROW_NUMBER 需排序，可能阻塞），考慮 OFFSET/FETCH (SQL 2012+) 或 Keyset 分頁（WHERE Id > @LastId ORDER BY Id LIMIT @PageSize）。
• ❌ 複雜多表 JOIN 分頁，建議先物化到臨時表/視圖再分頁。

版本演進：SQL Server 2012+ OFFSET/FETCH

SELECT @FieldName FROM @ViewName
WHERE @WhereString
ORDER BY @OrderString
OFFSET (@PageNo-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

語法更簡潔，執行計劃等價於 ROW_NUMBER，推薦新版本直接使用。