SQL Server 分頁查詢四大寫法演進與效能對比：從 NOT IN 到 OFFSET-FETCH

測試環境

資料表 ScoreInfo，主鍵 SID，假設每頁 5 筆，取第 3 頁（SID 11-15）。

四種寫法對比

方法一：NOT IN 子查詢（最直觀、效能最差）

SELECT TOP 5 * FROM ScoreInfo
WHERE SID NOT IN (SELECT TOP 10 SID FROM ScoreInfo ORDER BY SID)
ORDER BY SID

缺點： 掃描兩次全表，NOT IN 含 NULL 時邏輯陷阱，大數據量嚴重超時。

方法二：MAX(ID) 邊界值（略優於 NOT IN）

SELECT TOP 5 * FROM ScoreInfo
WHERE SID > (SELECT MAX(t.SID) FROM (SELECT TOP 10 SID FROM ScoreInfo ORDER BY SID) t)
ORDER BY SID

缺點： 仍需子查詢排序取 Top N，效能隨頁碼增大線性惡化（深分頁問題）。

方法三：ROW_NUMBER() 窗口函數（SQL 2005+ 標準寫法）

SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SID) AS RowNum
    FROM ScoreInfo
) t
WHERE t.RowNum BETWEEN 11 AND 15  -- (頁碼-1)*頁大小+1 至 頁碼*頁大小

優點： 單次掃描賦予行號，無深分頁效能退化，SQL 2005+ 通用。

方法四：OFFSET-FETCH（SQL 2012+ 現代標準，最推薦）

SELECT * FROM ScoreInfo
ORDER BY SID
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY  -- 跳過前 10 筆，取 5 筆

優點： 語法簡潔標準、優化器支援最佳、無子查詢開銷、可讀性最高。

效能綜合評分

深分頁優化進階技巧

• Keyset Pagination (游標分頁)： WHERE SID > @LastSeenID ORDER BY SID OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY，利用索引 Seek 直接定位，極大數據集翻頁無延遲。
• 覆蓋索引： 分頁查詢欄位全包含在索引中，避免 Key Lookup。
• 避免 SELECT *： 僅取必要欄位減少 I/O。

總結

• SQL 2012+ 專案直接用 OFFSET-FETCH，無需猶豫。
• 舊版專案用 ROW_NUMBER()，勿用 NOT IN/MAX(ID)。
• 極大數據集（百萬級+）考慮 Keyset Pagination 徹底解決深分頁。