SQL Server 2005 引入 `ROW_NUMBER()` 視窗函數,徹底改寫分頁查詢寫法。相較舊式 `TOP + NOT IN` / `MAX(ID)` 等模式,視窗函數具備**單次掃描、計畫穩定、可索引優化**三大優勢,是現代分頁的標準解法。
CREATE PROCEDURE dbo.usp_GetPagedData
@PageIndex INT = 1, -- 1-based 頁碼
@PageSize INT = 20, -- 每頁筆數
@TotalPages INT OUTPUT, -- 總頁數
@TotalRows BIGINT OUTPUT -- 總筆數
AS
BEGIN
SET NOCOUNT ON;
-- 1. 取總筆數 (可用索引統計資訊快速估算,或 COUNT_BIG)
SELECT @TotalRows = COUNT_BIG(*) FROM dbo.StudentsInfo;
SET @TotalPages = CEILING(@TotalRows * 1.0 / @PageSize);
-- 2. 視窗函數分頁 (單次掃描)
;WITH Numbered AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY ID) AS RowNum
FROM dbo.StudentsInfo
)
SELECT *
FROM Numbered
WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1
AND @PageIndex * @PageSize
ORDER BY RowNum; -- 保證輸出順序
END
| 方法 | 第 2 頁 | 第 1,000 頁 | 第 10,000 頁 | 第 100,000 頁 | 第 200,000 頁 | 穩定性 |
|---|---|---|---|---|---|---|
| TOP + NOT IN | 0 ms | 16 ms | 47 ms | 475 ms | 953 ms | 線性惡化 |
| TOP + MAX(ID) | 5 ms | 16 ms | 35 ms | 325 ms | 623 ms | 較佳 |
| 中間變量法 | 966 ms | 970 ms | 960 ms | 945 ms | 933 ms | 恆高 |
| ROW_NUMBER() | 0 ms | 0 ms | 34 ms | 365 ms | 710 ms | 優 |
| CTE + ROW_NUMBER | 780 ms | 796 ms | 798 ms | 780 ms | 805 ms | 恆高 |
當頁碼 > 總頁數/2 時,改用**倒序掃描 + 取前 N 行**再反轉:
DECLARE @Reverse BIT = CASE WHEN @PageIndex > @TotalPages / 2 THEN 1 ELSE 0 END;
;WITH Numbered AS (
SELECT *,
ROW_NUMBER() OVER (ORDER BY
CASE WHEN @Reverse = 1 THEN ID END DESC,
CASE WHEN @Reverse = 0 THEN ID END ASC
) AS RowNum
FROM dbo.StudentsInfo
)
SELECT * FROM Numbered
WHERE RowNum BETWEEN
CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex) * @PageSize + 1 ELSE (@PageIndex - 1) * @PageSize + 1 END
AND CASE WHEN @Reverse = 1 THEN (@TotalPages - @PageIndex + 1) * @PageSize ELSE @PageIndex * @PageSize END
ORDER BY ID ASC; -- 最終輸出永遠正序
測試顯示:第 199,999 頁從 710 ms 降至 **156 ms**,深分頁效能提升 **4-5 倍**,且首尾頁效能對稱。
SELECT * FROM dbo.StudentsInfo
ORDER BY ID
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
視窗函數分頁是 SQL Server 2005+ 的標準答案;配合雙向查找與覆蓋索引,可輕鬆支撐千萬級資料表的毫秒級分頁體驗。