SQL Server 分頁查詢三大經典方案效能比較與倒序優化

分頁是 Web 應用核心需求。本文整理三種主流 T-SQL 分頁寫法、效能特性，並針對「最新資料在前」的倒序場景給出優化寫法。

方案一：NOT IN + 子查詢 (最直觀、效能最差)

SELECT TOP @PageSize *
FROM table1
WHERE id NOT IN (
    SELECT TOP @PageSize * (@PageNum - 1) id
    FROM table1 ORDER BY id
)
ORDER BY id;

• 子查詢每次執行需排序並產生大量 NOT IN 比較，隨頁碼增大急劇變慢。
• 不支援 NULL 值 (NOT IN 含 NULL 回傳空集)。

方案二：MAX/MIN ID 邊界 + TOP (高效、需連續/單調 ID)

-- 正序
SELECT TOP @PageSize *
FROM table1
WHERE id > (
    SELECT ISNULL(MAX(id),0)
    FROM (SELECT TOP @PageSize * (@PageNum - 1) id FROM table1 ORDER BY id) t
)
ORDER BY id;

-- 倒序 (最新在前) —— 本文重點優化
SELECT TOP @PageSize *
FROM table1
WHERE id <= (
    SELECT ISNULL(MIN(id), (SELECT MAX(id) FROM table1))
    FROM (SELECT TOP @PageSize * (@PageNum - 1) id FROM table1 ORDER BY id DESC) t
)
ORDER BY id DESC;

• 利用叢集索引 (PK) 直接 Seek 到邊界，再 TOP 向前/向後掃描，IO 極少。
• 前提：ID 為單調遞增 (Identity/Sequence)，無大量刪除導致空洞過多。
• 倒序寫法關鍵：子查詢 ORDER BY id DESC 取前 N 頁的「最小 ID」作為上界，外層 WHERE id <= ... ORDER BY id DESC。

方案三：ROW_NUMBER() 視窗函數 (通用、SQL 2005+、效能中等)

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, *
    FROM table1
) t
WHERE rn > @PageSize * (@PageNum - 1) AND rn <= @PageSize * @PageNum;

• 支援任意排序欄位、複雜 JOIN、不依賴 ID 連續性。
• 需對全結果集排序並計算行號，大資料量 (百萬列) 記憶體/TempDB 壓力大。
• SQL 2012+ 可用 OFFSET ... FETCH 語法簡化，執行計畫相同。

效能比較總結 (單表、PK 叢集索引、100萬列、頁碼 50)

進階：游標/Keyset Pagination (API 介面推薦)

前端傳遞 last_seen_id，後端 WHERE id < @last_seen_id ORDER BY id DESC LIMIT @PageSize。無頁碼跳轉、無深分頁效能衰減、天然支援無限滾動。