SQL Server 2005 使用 ROW_NUMBER 實現分頁的兩種經典寫法

前言

SQL Server 2005 引入 ROW_NUMBER() 視窗函數，讓分頁查詢變得簡單且效能優異。本文介紹兩種最常用的分頁寫法，並解析其運作原理。

方法一：使用 BETWEEN 篩選

DECLARE @PageNumber INT = 2;
DECLARE @PageSize INT = 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;

原理：內層查詢為所有資料依 source_ip, id 排序並產生連續行號，外層以 BETWEEN 截取指定頁碼對應的行號區間。邏輯直觀，適合中小資料量。

方法二：使用 TOP 配合條件篩選

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

SELECT TOP 20 *
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;

原理：內層同樣產生行號，外層先以 row_num > 上一頁最後行號 過濾，再配合 TOP 20 取前 N 筆，最後按 row_num 排序確保順序。此寫法在某些執行計畫下可避免排序運算子，效能略優。

效能建議

• 確保 ORDER BY 欄位有適當索引（最佳為覆蓋索引），避免大量排序。
• 大資料量時，考慮使用 OFFSET ... FETCH（SQL Server 2012+）或 Keyset Pagination（游標式分頁）取代 OFFSET 分頁，減少深分頁掃描成本。
• 若分頁查詢頻繁且資料變動不大，可快取分頁結果或使用物化視圖。