SELECT TOP (@pageSize) *
FROM tb_TestTable
WHERE ID NOT IN (SELECT TOP (@pageSize * @pageIndex) ID FROM tb_TestTable ORDER BY ID)
ORDER BY ID;
缺點:`NOT IN` 含 `NULL` 語意陷阱、子查詢重複掃描、無法利用索引 Seek、隨頁碼增大線性惡化。
SELECT TOP (@pageSize) *
FROM tb_TestTable
WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP (@pageSize * @pageIndex) ID FROM tb_TestTable ORDER BY ID) AS T)
ORDER BY ID;
缺點:仍需掃描前 N 列取 MAX,深分頁效能差。
DECLARE @count INT = 0, @ID INT = 0;
SELECT @count = @count + 1, @ID = CASE WHEN @count <= @pageSize * @pageIndex THEN ID ELSE @ID END
FROM tb_TestTable ORDER BY ID;
SELECT TOP (@pageSize) * FROM tb_TestTable WHERE ID > @ID ORDER BY ID;
優點**:單次掃描、無子查詢嵌套。
缺點**:T-SQL 迴圈逐列處理極慢 (RBAR),200 萬列需數秒,不適合高並發。
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM tb_TestTable
) AS T
WHERE RowNum > @pageSize * @pageIndex
AND RowNum <= @pageSize * (@pageIndex + 1)
ORDER BY ID;
優點**:單次掃描、最佳化器可推導 `TOP` + `ORDER BY` 推播、支援並行計畫、語法標準、可維護性高。
測試結果**:各頁碼穩定 **< 50ms** (含首頁、尾頁)。
WITH CTE AS (
SELECT *,
CEILING(ROW_NUMBER() OVER (ORDER BY ID) * 1.0 / @pageSize) AS PageNum
FROM tb_TestTable
)
SELECT * FROM CTE WHERE PageNum = @pageIndex + 1; -- 1 基底頁碼
本質同寫法四,`CEILING` 計算頁碼直觀,效能相近。
| 寫法 | 首頁 | 中間頁 (1000) | 尾頁 | 評價 |
|---|---|---|---|---|
| NOT IN | 120ms | 3,800ms | 7,500ms | ❌ 淘汰 |
| MAX(ID) | 90ms | 2,500ms | 5,200ms | ❌ 淘汰 |
| 變數迴圈 | 1,200ms | 1,200ms | 1,200ms | ⚠️ 固定高成本,RBAR |
| ROW_NUMBER() | 15ms | 18ms | 22ms | ✅ 首選 |
| CTE + CEILING | 16ms | 19ms | 23ms | ✅ 推薦 |
-- OFFSET-FETCH 標準語法 (ANSI SQL:2008)
SELECT * FROM tb_TestTable
ORDER BY ID
OFFSET @pageSize * @pageIndex ROWS
FETCH NEXT @pageSize ROWS ONLY;