SQL Server 2005 ROW_NUMBER() 分頁查詢兩種經典寫法：BETWEEN 與 TOP

SQL Server 2005 引入 ROW_NUMBER() 視窗函數，終結 SQL 2000 時代複雜的分頁查詢寫法。本文介紹兩種最常用的分頁模式：BETWEEN 範圍過濾與 TOP 結合 WHERE，並提供效能比較與參數化範本。

測試環境建立

CREATE TABLE tb (
    id INT IDENTITY(1,1),
    source_ip VARCHAR(50),
    data_col VARCHAR(100)
);
-- 假設已有大量測試資料

寫法一：BETWEEN 範圍過濾 (直觀、易讀)

DECLARE @PageNumber INT = 2;   -- 第 2 頁
DECLARE @PageSize INT = 20;    -- 每頁 20 筆

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY source_ip, id) AS row_num, *
    FROM tb
) AS page_table
WHERE row_num BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize;

寫法二：TOP + WHERE (舊版相容、部分優化器友善)

DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 20;

SELECT TOP (@PageSize) *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY source_ip, id) AS row_num, *
    FROM tb
) AS page_table
WHERE row_num > (@PageNumber - 1) * @PageSize
ORDER BY row_num;

執行計畫與效能比較

深分頁效能陷阱：無論哪種寫法，OFFSET 1000000 ROWS FETCH NEXT 20 (SQL 2012+) 或 ROW_NUMBER() > 1000000 都需掃描前 100 萬列。解法：鍵值分頁 (Keyset Pagination / Seek Method) — 記住上一頁最大排序鍵值，下一頁用 WHERE sort_key > @last_max 配合索引 Seek。

鍵值分頁範例 (高效能深分頁)

-- 第一頁
SELECT TOP (20) * FROM tb ORDER BY source_ip, id;

-- 後續頁 (假設上一頁最大 source_ip='192.168.1.100', id=5000)
DECLARE @last_ip VARCHAR(50) = '192.168.1.100';
DECLARE @last_id INT = 5000;

SELECT TOP (20) *
FROM tb
WHERE source_ip > @last_ip 
   OR (source_ip = @last_ip AND id > @last_id)
ORDER BY source_ip, id;

前提：(source_ip, id) 有索引，查詢直接 Seek 到位置，O(log N) + 20 行，不受頁碼影響。

參數化預存程序範本

CREATE PROC dbo.usp_GetPagedData
    @PageNumber INT = 1,
    @PageSize INT = 20,
    @SortColumn SYSNAME = 'source_ip',
    @SortOrder VARCHAR(4) = 'ASC'
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 動態 SQL 組裝 ORDER BY (防注入需驗證 @SortColumn 白名單)
    DECLARE @sql NVARCHAR(MAX) = N'
    SELECT * FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@SortColumn) + ' ' + @SortOrder + ') AS rn, *
        FROM tb
    ) t WHERE rn BETWEEN (@PageNumber - 1) * @PageSize + 1 AND @PageNumber * @PageSize;';
    
    EXEC sp_executesql @sql,
        N'@PageNumber INT, @PageSize INT',
        @PageNumber, @PageSize;
END

SQL 2012+ 現代語法 (OFFSET/FETCH)

SELECT * FROM tb
ORDER BY source_ip, id
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

語法更簡潔，執行計畫與 ROW_NUMBER() 類似，深分頁同樣有效能問題，仍建議鍵值分頁。

總結建議

• 一般分頁 (前 100 頁)：BETWEEN 寫法 最易維護
• 舊版相容/特定優化器偏好：TOP 寫法
• 深分頁 / 高併發 / 大資料集：鍵值分頁 (需應用層配合記住上一頁游標)
• SQL 2012+ 新專案：OFFSET/FETCH 標準語法
• 永遠在 ORDER BY 欄位建立索引，避免 Sort 操作子