SQL Server 通用分頁存儲過程：ROW_NUMBER() 與傳統方法效能深度解析

前言

分頁查詢是資料庫應用中最常見的需求之一。在 SQL Server 2000 時代，開發者常使用 NOT IN 或 MAX(ID) 等技巧實現分頁；而 SQL Server 2005 引入 ROW_NUMBER() 視窗函數後，分頁寫法變得更簡潔且效能更優。本文將透過實際測試，對比兩種分頁存儲過程在 50 萬筆資料下的效能表現，並分析統計總筆數對效能的影響。

測試環境與資料準備

資料表結構

CREATE TABLE [dbo].[Xtest](
    [ID] [bigint] IDENTITY(1,1) NOT NULL,
    [XName] [nvarchar](50) NULL,
    [XDemo] [nvarchar](500) NULL,
    CONSTRAINT [PK_xtest] PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

插入 50 萬筆測試資料

DECLARE @i INT = 1
WHILE @i < 500001
BEGIN
    INSERT INTO dbo.Xtest (XName, XDemo)
    VALUES (CAST(@i AS VARCHAR) + 'name' + CAST(@i AS VARCHAR),
            N'這是第' + CAST(@i AS VARCHAR) + N'的內容')
    SET @i = @i + 1
END

另對 XName 欄位建立非叢集索引以模擬實際查詢場景。

兩種分頁存儲過程實作

1. SQL Server 2000 相容版本（不使用 ROW_NUMBER）

採用 NOT IN (SELECT TOP ...) 子查詢方式跳過前幾頁資料：

ALTER PROCEDURE [dbo].[sp_Pager2000]
    @tblName VARCHAR(255),
    @strGetFields VARCHAR(1000) = '*',
    @pkName NVARCHAR(50) = 'ID',
    @strOrder VARCHAR(255) = '',
    @strWhere VARCHAR(1500) = '',
    @pageIndex INT = 1,
    @pageSize INT = 20,
    @recordCount INT = 0 OUTPUT,
    @doCount BIT = 0
AS
BEGIN
    -- 動態 SQL 組裝邏輯，核心為：
    -- SELECT TOP @pageSize ... WHERE @pkName NOT IN (SELECT TOP (@pageSize*(@pageIndex-1)) ...)
    -- 完整代碼見原文
END

2. SQL Server 2005+ 版本（使用 ROW_NUMBER()）

利用視窗函數產生行號，再以 BETWEEN 篩選目標頁：

ALTER PROCEDURE [dbo].[sp_Pager2005]
    @tblName VARCHAR(255),
    @strGetFields VARCHAR(1000) = '*',
    @strOrder VARCHAR(255) = '',
    @strWhere VARCHAR(1500) = '',
    @pageIndex INT = 1,
    @pageSize INT = 20,
    @recordCount INT OUTPUT,
    @doCount BIT = 0
AS
BEGIN
    DECLARE @strSQL VARCHAR(5000)
    -- 統計總筆數邏輯同上
    -- 核心查詢：
    SET @strSQL = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (' + @strOrder + ') AS ROWID, '
                + @strGetFields + ' FROM [' + @tblName + '] ' + @strWhere + ') AS sp '
                + 'WHERE ROWID BETWEEN ' + STR((@pageIndex-1)*@pageSize+1)
                + ' AND ' + STR(@pageIndex*@pageSize)
    EXEC (@strSQL)
END

效能測試結果

測試條件：WHERE XName LIKE '%222name%'，分頁大小 20 筆，測試第 1~6 頁。環境：Core 2 Duo 2.20GHz, 1GB RAM, SQL Server 2008。

情境 A：每頁都統計總筆數（@doCount=1）

結論：每頁皆執行 COUNT(*) 會導致效能隨頁碼遞增惡化，兩種寫法差異不大，瓶頸在於重複統計。

情境 B：僅第一頁統計總筆數，後續頁重用結果（@doCount=0）

結論：避免重複統計總筆數後，ROW_NUMBER() 版本效能顯著優於傳統 NOT IN 寫法，且隨頁碼增長效能衰減較緩慢。

核心優化建議

1. 總筆數快取：首次查詢時統計總筆數並快取（Session、Redis 或應用層），後續分頁請求直接使用快取值，避免重複 COUNT(*) 全表掃描。
2. 優先使用 ROW_NUMBER()：SQL Server 2005 以上版本應採用視窗函數分頁，執行計劃更穩定，且不易受參數嗅探影響。
3. 索引覆蓋：確保 ORDER BY 與 WHERE 條件欄位有適當索引，必要時建立覆蓋索引減少 Key Lookup。
4. 大資料量深分頁優化：若頁碼極大（如第 10000 頁），考慮使用「游標式分頁」或「延遲關聯」技巧：先只查主鍵再關聯回表。

總結

分頁效能的關鍵在於避免重複統計總筆數與選擇高效的分頁語法。實測證明：搭配 ROW_NUMBER() 並快取總筆數，可將後續頁面回應時間從秒級降至毫秒級。開發者應根據 SQL Server 版本選擇適當方案，並將統計邏輯從分頁存儲過程中剝離，由應用層管理。