SQL Server 2005 分頁查詢新寫法：NTILE 與 ROW_NUMBER 效能對比

SQL Server 2005 引入 NTILE() 與 ROW_NUMBER() 視窗函數，讓分頁查詢不再需要複雜的 TOP + 子查詢巢狀結構。本文展示兩種寫法並討論效能差異。

方法一：NTILE (依總頁數分桶)

DECLARE @RowsPerPage INT = 20;
DECLARE @CurrentPage INT = 2;
DECLARE @TotalPages  INT;

-- 計算總頁數
SELECT @TotalPages = COUNT(*) / @RowsPerPage FROM testtable;

-- 分頁查詢
WITH t AS (
    SELECT NTILE(@TotalPages) OVER (ORDER BY id) AS page_number, *
    FROM testtable
)
SELECT * FROM t WHERE page_number = @CurrentPage;

缺點：需先計算總頁數 (額外掃描)，且 NTILE 桶數固定，資料分布不均時頁面筆數可能不一致。

方法二：ROW_NUMBER (標準分頁寫法，推薦)

DECLARE @RowsPerPage INT = 20;
DECLARE @CurrentPage INT = 2;
DECLARE @StartRow    INT = (@CurrentPage - 1) * @RowsPerPage;

WITH t AS (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS row_number, *
    FROM testtable
)
SELECT * FROM t
WHERE row_number BETWEEN @StartRow + 1 AND @StartRow + @RowsPerPage;

優點：單次掃描、精確控制每頁筆數、可搭配 OFFSET/FETCH (2012+) 更簡潔。

效能對比建議

SQL Server 2012+ 現代寫法 (最簡潔)

SELECT * FROM testtable
ORDER BY id
OFFSET (@CurrentPage - 1) * @RowsPerPage ROWS
FETCH NEXT @RowsPerPage ROWS ONLY;

索引建議

分頁查詢的 ORDER BY 欄位務必建立索引 (如 CREATE INDEX IX_testtable_id ON testtable(id))，否則需全表排序，效能隨資料量線性惡化。