測試環境與基線
- 表
ARTICLE：53,210 行，聚集索引 ID，非索引列 YEAR。
- 分頁大小：30 行。
- 對比場景：第 1,000 條（前 34 頁）vs 第 45,001 條（第 1,500 頁）。
- SQL Server 2008 R2，冷緩存各跑 100 次取平均耗時。
五種分頁寫法與測試結果
| 方案 | 核心 SQL 模式 | 第 1000 行 (ms) | 第 45000 行 (ms) | 綜合評價 |
|---|
| 1. NOT IN 子查詢 | TOP N WHERE ID NOT IN (TOP M ...) | 80,000 | 45,000 | ❌ 深分頁災難，掃描量隨頁碼線性增長 |
| 2. 雙重 TOP 正反序 | TOP N FROM (TOP M ... ORDER BY DESC) ORDER BY ASC | 30,000 | 138,000 | ❌ 深分頁更差，雙重排序開銷大 |
| 3. 衍生表 JOIN 只取鍵 | SELECT * FROM T JOIN (TOP N ID FROM (TOP M ID, YEAR ...) w) w2 | 12,000 | 21,000 | ✅ 前中頁優秀，深分頁尚可 |
| 4. WHERE ID IN 衍生表 | SELECT * FROM T WHERE ID IN (TOP N ID FROM (TOP M ...) w) | 13,000 | 20,000 | ✅ 與方案 3 齊頭並進，可讀性稍好 |
| 5. ROW_NUMBER 窗口函數 | SELECT w2.n, w1.* FROM T w1 JOIN (TOP M ROW_NUMBER()...) w2 ON w1.ID=w2.ID WHERE w2.n > offset | 14,000 | 15,000 | 🏆 深分頁穩定最優，2012+ 首選 |
結論與選型建議
- SQL Server 2012+：首選方案 5 (OFFSET-FETCH / ROW_NUMBER)，語法簡潔、深淺分頁性能穩定、優化器支持良好。
- SQL Server 2008/2005：方案 3/4 (衍生表只取鍵值 JOIN/IN) 是最佳折中，避免全表排序與大掃描。
- 極小頁碼（前 10 頁）：方案 3/4/5 差異微小，可按團隊習慣選擇。
- 避免方案 1/2：隨頁碼增長性能急劇惡化，生產環境禁用。
通用分頁存儲過程（基於方案 4，兼容 2005+）
CREATE PROCEDURE [dbo].[usp_Pager_v2]
@TableName NVARCHAR(100), -- 表名（含架構如 dbo.Orders）
@KeyColumn NVARCHAR(50), -- 主鍵/唯一鍵列
@Fields NVARCHAR(500), -- 選擇列，* 或列表
@WhereClause NVARCHAR(3000), -- WHERE 條件（不含 WHERE 關鍵字）
@OrderBy NVARCHAR(100), -- ORDER BY 列（不含 ORDER BY）
@PageIndex INT, -- 1-based 頁碼
@PageSize INT, -- 頁大小
@TotalRows BIGINT OUTPUT, -- 總行數
@TotalPages BIGINT OUTPUT -- 總頁數
AS
BEGIN
SET NOCOUNT ON;
IF @PageSize <= 0 OR @PageIndex <= 0 RETURN;
DECLARE @StartRow BIGINT = (@PageIndex - 1) * @PageSize + 1;
DECLARE @EndRow BIGINT = @StartRow + @PageSize - 1;
DECLARE @Where NVARCHAR(3000) = CASE WHEN @WhereClause = '' THEN '' ELSE ' WHERE ' + @WhereClause END;
DECLARE @Order1 NVARCHAR(200), @Order2 NVARCHAR(200);
-- 構建正反序 ORDER BY
IF @OrderBy <> ''
BEGIN
SET @Order1 = ' ORDER BY ' + REPLACE(REPLACE(@OrderBy, 'DESC', ''), 'ASC', '') + ' DESC';
SET @Order2 = ' ORDER BY ' + @OrderBy;
END
ELSE
BEGIN
SET @Order1 = ' ORDER BY ' + @KeyColumn + ' DESC';
SET @Order2 = ' ORDER BY ' + @KeyColumn + ' ASC';
END
-- 統計總行數
DECLARE @SqlCount NVARCHAR(4000) = 'SELECT @TotalRows = COUNT(*) FROM ' + @TableName + @Where;
EXEC sp_executesql @SqlCount, N'@TotalRows BIGINT OUTPUT', @TotalRows OUTPUT;
SET @TotalPages = CASE WHEN @TotalRows % @PageSize = 0 THEN @TotalRows / @PageSize ELSE @TotalRows / @PageSize + 1 END;
IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages; -- 修正越界
-- 分頁查詢（方案 4 模式）
DECLARE @Sql NVARCHAR(MAX) = '
SELECT ' + @Fields + ' FROM ' + @TableName + ' t1
WHERE ' + @KeyColumn + ' IN (
SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' + @KeyColumn + ' FROM (
SELECT TOP ' + CAST(@EndRow AS NVARCHAR) + ' ' + @KeyColumn + ' FROM ' + @TableName + @Where + @Order2 + '
) t ' + @Order1 + '
)' + @Order2;
EXEC sp_executesql @Sql;
END;
調用示例
DECLARE @Rows BIGINT, @Pages BIGINT;
EXEC usp_Pager_v2
@TableName = 'dbo.Orders',
@KeyColumn = 'OrderID',
@Fields = 'OrderID, CustomerID, OrderDate, TotalAmount',
@WhereClause = 'OrderDate >= ''2024-01-01'' AND Status = 1',
@OrderBy = 'OrderDate DESC',
@PageIndex = 3,
@PageSize = 20,
@TotalRows = @Rows OUTPUT,
@TotalPages = @Pages OUTPUT;
SELECT @Rows AS TotalRows, @Pages AS TotalPages;
