SQL Server 分頁查詢三大經典寫法與效能對比 (TOP/NOT IN/ROW_NUMBER)

分頁核心參數

• @PageSize：每頁筆數
• @PageIndex：頁碼 (1-based)
• @StartRow = (@PageIndex-1)*@PageSize + 1
• @EndRow = @PageIndex*@PageSize

寫法一：TOP + NOT IN (最直觀、效能最差)

SELECT TOP (@PageSize) *
FROM tbl
WHERE id NOT IN (SELECT TOP (@PageSize * @PageIndex) id FROM tbl ORDER BY id)
ORDER BY id;

• ✅ 邏輯簡單，不需額外欄位。
• ❌ 子查詢執行計畫差，大資料量極慢，NOT IN 遇 `NULL` 還有語義陷阱。

寫法二：暫存表 + IDENTITY (兩階段、中等效能)

SELECT IDENTITY(INT, 1, 1) AS RowNum, * INTO #tmp FROM tbl ORDER BY id;
SELECT * FROM #tmp WHERE RowNum BETWEEN @StartRow AND @EndRow;
DROP TABLE #tmp;

• ✅ 可重複使用暫存表結果 (同一連線多次分頁)。
• ❌ 需寫入 `tempdb`，產生 I/O、鎖定、記錄檔壓力；高併發易造成 `tempdb` 爭用。

寫法三：巢狀 TOP + 反向排序 (單查詢、效能較好)

SELECT TOP (@PageSize) *
FROM (
    SELECT TOP (@PageSize * @PageIndex) *
    FROM tbl
    ORDER BY id ASC
) AS t
ORDER BY id DESC;

• ✅ 單一陳述式，無 `tempdb` 開銷，利用索引順序掃描。
• ⚠️ 外層 `ORDER BY DESC` 可能導致額外排序運算子 (視索引而定)。

現代最佳解：ROW_NUMBER() / OFFSET-FETCH (SQL 2005+/2012+)

ROW_NUMBER() 通用寫法 (SQL 2005+)

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS RN FROM tbl
)
SELECT * FROM CTE WHERE RN BETWEEN @StartRow AND @EndRow;

OFFSET-FETCH 簡潔寫法 (SQL 2012+)

SELECT * FROM tbl
ORDER BY id
OFFSET (@PageIndex-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY;

效能對比總結 (百萬級測試參考)

深分頁優化技巧 (第 10 萬頁)

1. 延遲關聯：先用索引鍵分頁取得 PK，再 `JOIN` 回表取明細。

   SELECT t.* FROM tbl t
   JOIN (SELECT id FROM tbl ORDER BY id OFFSET 1000000 ROWS FETCH NEXT 20 ROWS ONLY) p ON t.id = p.id;

2. 游標分頁：前端傳遞 `last_seen_id`，查詢 `WHERE id > @last_id ORDER BY id LIMIT @PageSize` (需唯一遞增鍵)。
3. 覆蓋索引：建立 `INCLUDE` 所有 `SELECT` 欄位的索引，避免書籤查找。

總結

**SQL 2012+ 請直接用 `OFFSET-FETCH`**；舊版用 `ROW_NUMBER()`。避免 `NOT IN`、`tempdb` 方案。深分頁採用「延遲關聯」或「游標式」設計，從根源消除大量掃描。