SQL Server 分頁存儲過程五種寫法效能大比拼：ROW_NUMBER 完勝

測試環境

• 表 `tb_TestTable`：200 萬行，主鍵 `ID` 叢集索引。
• 分頁參數：`@PageIndex`、 `@PageSize`。
• 指標：邏輯讀取、CPU 時間、耗時 (ms)。

五種寫法原始碼

1. TOP + NOT IN (最慢)

CREATE PROC proc_paged_notin @PageIndex INT, @PageSize INT
AS
BEGIN
    DECLARE @sql NVARCHAR(500);
    SET @sql = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM tb_TestTable
                WHERE ID NOT IN (SELECT TOP ' + CAST(@PageSize*@PageIndex AS VARCHAR) + ' ID FROM tb_TestTable ORDER BY ID)
                ORDER BY ID';
    EXEC sp_executesql @sql;
END;

2. TOP + MAX(ID) 子查詢

CREATE PROC proc_paged_maxid @PageIndex INT, @PageSize INT
AS
BEGIN
    DECLARE @sql NVARCHAR(500);
    SET @sql = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' * FROM tb_TestTable
                WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP ' + CAST(@PageSize*@PageIndex AS VARCHAR) + ' ID FROM tb_TestTable ORDER BY ID) t)
                ORDER BY ID';
    EXEC sp_executesql @sql;
END;

3. 變數迴圈累積最大 ID (舊版黑科技)

CREATE PROC proc_paged_var @PageIndex INT, @PageSize INT
AS
BEGIN
    DECLARE @cnt INT = 0, @maxID INT = 0;
    SELECT @cnt = @cnt + 1, @maxID = CASE WHEN @cnt <= @PageSize*@PageIndex THEN ID ELSE @maxID END
    FROM tb_TestTable ORDER BY ID;
    EXEC ('SELECT TOP ' + @PageSize + ' * FROM tb_TestTable WHERE ID > ' + @maxID);
END;

4. ROW_NUMBER() 視窗函數 (SQL 2005+ 標準)

CREATE PROC proc_paged_rownum @PageIndex INT, @PageSize INT
AS
BEGIN
    SELECT * FROM (
        SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RN FROM tb_TestTable
    ) t WHERE RN BETWEEN (@PageIndex-1)*@PageSize + 1 AND @PageIndex*@PageSize;
END;

5. CTE + ROW_NUMBER() 計算頁碼 (可複用頁碼欄位)

CREATE PROC proc_paged_cte @PageIndex INT, @PageSize INT
AS
BEGIN
    DECLARE @sql NVARCHAR(MAX);
    SET @sql = 'WITH CTE AS (
        SELECT CEILING(ROW_NUMBER() OVER (ORDER BY ID) * 1.0 / ' + @PageSize + ') AS PageNum, *
        FROM tb_TestTable
    ) SELECT * FROM CTE WHERE PageNum = ' + @PageIndex;
    EXEC sp_executesql @sql;
END;

效能測試結果 (200 萬行、第 50 頁、每頁 20 筆)

深度解析：為何 ROW_NUMBER() 完勝？

1. 索引順序掃描 + Top-N 排序優化：優化器識別 `ROW_NUMBER() OVER (ORDER BY PK)` 可直接走叢集索引順序掃描，配合 `TOP` 內部最佳化，僅讀取必要頁面。
2. 無臨時物件、無自我連接、無多次掃描：單一查詢計畫，記憶體授予穩定。
3. 可並行：大表可利用並行查詢 (CXPACKET) 進一步加速。

現代最佳寫法：OFFSET-FETCH (SQL 2012+)

CREATE PROC proc_paged_modern @PageIndex INT, @PageSize INT
AS
BEGIN
    SELECT * FROM tb_TestTable
    ORDER BY ID
    OFFSET (@PageIndex-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;
END;

• 語法最簡潔，執行計畫與 `ROW_NUMBER()` 基本一致，效能持平。
• **新專案強制使用此寫法**。

深分頁 (百萬頁) 專用優化：延遲關聯

SELECT t.*
FROM tb_TestTable t
INNER JOIN (
    SELECT ID FROM tb_TestTable ORDER BY ID
    OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY
) p ON t.ID = p.ID;

內層僅掃描索引鍵 (極窄)，外層精準 `JOIN` 回表，避免掃描海量中間行。

總結

**棄用 `NOT IN`、`MAX(ID)`、變數迴圈、臨時表**——它們在百萬級以上資料量前毫無競爭力。