SQL Server 分頁查詢最佳實踐：使用 ROW_NUMBER() 視窗函數

SQL Server 2005 引入的 ROW_NUMBER() 視窗函數，提供了標準、高效的分頁解決方案，取代了舊版複雜的 TOP ... NOT IN 或游標方式。本文透過預存程序範例詳細說明實作細節。

分頁預存程序完整範例

CREATE PROCEDURE [dbo].[usp_GetStuPage]
    @pageIndex INT = 1,      -- 當前頁碼 (從 1 開始)
    @pageSize  INT = 10,     -- 每頁筆數
    @pageCount INT OUTPUT    -- 輸出總頁數
AS
BEGIN
    SET NOCOUNT ON

    -- 1. 計算總頁數
    SELECT @pageCount = CEILING(COUNT(*) * 1.0 / @pageSize)
    FROM StudentsInfo

    -- 2. 核心分頁查詢：ROW_NUMBER() + 子查詢篩選
    SELECT * FROM (
        SELECT *,
               ROW_NUMBER() OVER (ORDER BY id) AS rownum
        FROM StudentsInfo
    ) AS t
    WHERE t.rownum BETWEEN (@pageIndex - 1) * @pageSize + 1 
                       AND @pageIndex * @pageSize
END

關鍵技術點解析

1. ROW_NUMBER() OVER (ORDER BY ...) 核心語法

• OVER 子句強制要求 ORDER BY，決定排序依據，確保分頁結果穩定
• 產生連續遞增序號 (1, 2, 3...)，不受 WHERE 子句影響
• 支援 PARTITION BY 進行分組排序（如各類別內分頁）

2. 篩選邊界計算公式

起始行 = (@pageIndex - 1) * @pageSize + 1
結束行 = @pageIndex * @pageSize

使用 BETWEEN 區間篩選，語意清晰且效能佳。

3. 總頁數計算技巧

CEILING(COUNT(*) * 1.0 / @pageSize)：
* 1.0 強制隱含轉換為浮點數，避免整數除法截斷誤差。

進階優化：大資料量分頁效能提升

方案 A：鍵值集分頁 - 避免掃描大量前置行

-- 適用於「下一頁」導航，需記住上一頁最後一筆的排序鍵值
SELECT TOP (@pageSize) *
FROM StudentsInfo
WHERE id > @lastSeenId  -- 上一頁最大 id
ORDER BY id

優點：不隨頁碼增大而變慢，適合無限捲動/翻頁場景。
缺點：不支援跳轉任意頁碼。

方案 B：覆蓋索引優化

為 ORDER BY 欄位建立索引，並 INCLUDE 查詢所需欄位：

CREATE INDEX IX_StudentsInfo_Paging ON StudentsInfo (id) INCLUDE (name, age, ...)

使查詢走 Index Seek + Key Lookup 或直接 Covering Index Scan，避免聚集索引掃描。

方案 C：OFFSET/FETCH (SQL Server 2012+)

SELECT * FROM StudentsInfo
ORDER BY id
OFFSET (@pageIndex - 1) * @pageSize ROWS
FETCH NEXT @pageSize ROWS ONLY

語法更簡潔，執行計畫與 ROW_NUMBER() 相同，建議新專案優先使用。

常見陷阱與避坑指南

問題	原因	解決方案
第 1 萬頁查詢極慢	需掃描前 10 萬行才能取得目標區間	改用鍵值集分頁或增加過濾條件縮小範圍
並發環境下資料重複/遺漏	分頁間隔有資料異動	使用快照隔離級別或應用層處理
ORDER BY 欄位非唯一	相同排序值導致行序不穩定	加上主鍵作為第二排序鍵：ORDER BY created_at, id

總結

ROW_NUMBER() 視窗函數是 SQL Server 2005-2008 R2 時代的標準分頁方案；SQL Server 2012+ 建議直接使用 OFFSET/FETCH。無論哪種語法，合適的索引設計才是分頁效能的關鍵。