面對千萬級資料量,傳統 `TOP N` + `NOT IN` / `ROW_NUMBER()` (2005+) 分頁在深分頁 (第 1000 頁) 時效能崩塌。本文解析一個基於 **「記錄排序鍵值定位 + ROWCOUNT 限制」** 的經典分頁存儲過程,適用 SQL Server 2000/2005/2008/R2,並對比現代 `OFFSET/FETCH` (2012+) 方案。
**優勢**:避免掃描並丟棄前 N 頁資料,直接 **Index Seek 定位** 到分頁起點,效能隨頁碼增長**不退化** (O(1) 定位 vs O(N) 掃描)。
IF OBJECT_ID('dbo.SP_Pagination', 'P') IS NOT NULL DROP PROCEDURE dbo.SP_Pagination;
GO
CREATE PROCEDURE dbo.SP_Pagination
@Tables VARCHAR(2000), -- 表/視圖名 (含 Schema 建議)
@PrimaryKey VARCHAR(500), -- 主鍵/唯一鍵 (作為最終輸出排序保證)
@Sort VARCHAR(500) = NULL, -- 排序欄位 (不含 ORDER BY),如 'OrderDate DESC, Id ASC'
@CurrentPage INT = 1,
@PageSize INT = 50,
@Fields VARCHAR(2000) = '*', -- 輸出欄位
@Filter VARCHAR(1000) = NULL, -- WHERE 條件 (不含 WHERE)
@Group VARCHAR(1000) = NULL -- GROUP BY 條件 (不含 GROUP BY)
AS
BEGIN
SET NOCOUNT ON;
-- 1. 預設排序 = 主鍵
IF @Sort IS NULL OR @Sort = '' SET @Sort = @PrimaryKey;
-- 2. 解析排序方向與欄位 (簡化版,假設單欄位排序或首欄位決定方向)
DECLARE @SortColumn NVARCHAR(500), @Operator NCHAR(2), @SortTable NVARCHAR(500), @SortName NVARCHAR(500);
DECLARE @DataType NVARCHAR(100), @Precision INT;
IF CHARINDEX('DESC', @Sort) > 0 BEGIN
SET @SortColumn = LTRIM(RTRIM(REPLACE(@Sort, 'DESC', '')));
SET @Operator = N'<='; -- 降冪:下一頁值 <= 上頁最小值
END ELSE BEGIN
IF CHARINDEX('ASC', @Sort) = 0 SET @Sort = @Sort + ' ASC'; -- 明確指定 ASC
SET @SortColumn = LTRIM(RTRIM(REPLACE(@Sort, 'ASC', '')));
SET @Operator = N'>='; -- 升冪:下一頁值 >= 上頁最大值
END
-- 3. 解析 表.欄位 格式
IF CHARINDEX('.', @SortColumn) > 0 BEGIN
SET @SortTable = SUBSTRING(@SortColumn, 1, CHARINDEX('.', @SortColumn)-1);
SET @SortName = SUBSTRING(@SortColumn, CHARINDEX('.', @SortColumn)+1, LEN(@SortColumn));
END ELSE BEGIN
SET @SortTable = @Tables;
SET @SortName = @SortColumn;
END
-- 4. 獲取排序欄位資料型別與精度 (用於變數宣告)
SELECT @DataType = t.name, @Precision = c.prec
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xusertype = t.xusertype
WHERE o.name = @SortTable AND c.name = @SortName;
IF CHARINDEX('char', @DataType) > 0
SET @DataType = @DataType + '(' + CAST(@Precision AS VARCHAR(10)) + ')';
-- 5. 參數處理
IF @CurrentPage < 1 SET @CurrentPage = 1;
DECLARE @StartRow VARCHAR(20) = CAST((@CurrentPage - 1) * @PageSize + 1 AS VARCHAR(20));
DECLARE @PageSizeStr VARCHAR(20) = CAST(@PageSize AS VARCHAR(20));
DECLARE @FilterStr NVARCHAR(1100) = CASE WHEN @Filter IS NOT NULL AND @Filter <> '' THEN N' WHERE ' + @Filter + N' ' ELSE N'' END;
DECLARE @SimpleFilter NVARCHAR(1100) = CASE WHEN @Filter IS NOT NULL AND @Filter <> '' THEN N' AND ' + @Filter + N' ' ELSE N'' END;
DECLARE @GroupStr NVARCHAR(1100) = CASE WHEN @Group IS NOT NULL AND @Group <> '' THEN N' GROUP BY ' + @Group + N' ' ELSE N'' END;
-- 6. 動態 SQL 執行 (核心雙查詢)
DECLARE @DynamicSql NVARCHAR(MAX) = N'
DECLARE @SortVal ' + @DataType + N';
SET ROWCOUNT ' + @StartRow + N';
SELECT @SortVal = ' + @SortColumn + N' FROM ' + @Tables + @FilterStr + @GroupStr + N' ORDER BY ' + @Sort + N';
SET ROWCOUNT ' + @PageSizeStr + N';
SELECT ' + @Fields + N' FROM ' + @Tables + N' WHERE ' + @SortColumn + @Operator + N' @SortVal ' + @SimpleFilter + @GroupStr + N' ORDER BY ' + @Sort + N';';
-- PRINT @DynamicSql; -- 除錯用
EXEC sp_executesql @DynamicSql;
-- 7. 重置 ROWCOUNT (關鍵!避免影響後續連接池連接)
SET ROWCOUNT 0;
END
GO
-- 表 Traffic_Sites,主鍵 Id,依 Id 升冪分頁,第 1 頁 5 筆
EXEC dbo.SP_Pagination
@Tables = 'Traffic_Sites',
@PrimaryKey = 'Id',
@Sort = 'Id ASC',
@CurrentPage = 1,
@PageSize = 5,
@Fields = '*',
@Filter = '',
@Group = '';
| 限制/風險 | 說明 | 緩解方案 |
|---|---|---|
| 排序鍵必須唯一 | 若排序欄位有重複值,`WHERE SortCol >= @LastVal` 會漏掉同值的後續行,或重複返回同值行。 | **排序欄位必須包含唯一鍵** (如 `OrderDate DESC, Id DESC`),`@PrimaryKey` 參數即為此用途,但舊版代碼未自動組合,需呼叫端保證 `@Sort` 含唯一鍵。 |
| SET ROWCOUNT 影響範圍 | `SET ROWCOUNT` 為連接級會話設定,影響後續所有語句,甚至連接池復用時殘留。 | **結尾必須 `SET ROWCOUNT 0` 重置**。現代版本改用 `TOP (@Var)` 避免此問題。 |
| 動態 SQL 注入風險 | `@Tables`、`@Sort`、`@Filter`、`@Group` 直接拼接。 | 嚴格白名單驗證表名/欄位名;`@Filter`/`@Group` 建議改用 Table-Valued Parameter 或應用層構建安全 SQL。 |
| 統計資訊依賴 | 優化器需準確統計資訊選擇正確索引 Seek。 | 定期 `UPDATE STATISTICS` 或啟用自動更新。 |
| 不支援複雜排序 | 多欄位混合 ASC/DESC (如 `Col1 ASC, Col2 DESC`) 邏輯極複雜,此過程僅處理首欄位方向。 | 升級至 SQL 2012+ 使用 `OFFSET/FETCH` 原生支援任意複雜排序。 |
| 視圖/衍生表支援有限 | 註釋提到視圖「目前還有點小問題」。 | 視圖需確保排序欄位來自基礎表且有索引支援。 |
-- 標準、簡潔、安全、支援任意複雜排序、無 ROWCOUNT 副作用
DECLARE @PageSize INT = 50, @PageNumber INT = 3;
SELECT Id, Col1, Col2
FROM dbo.BigTable
WHERE Status = 1
ORDER BY CreatedDate DESC, Id DESC -- 必須含唯一鍵保證確定性
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;
此存儲過程是 SQL 2000/2005 時代 **高效能分頁的經典之作**,核心思想「鍵值定位 + ROWCOUNT」至今仍是分散式資料庫分頁 (如 `WHERE id > last_seen_id`) 的基石。**現代環境 (SQL 2012+) 請直接使用 `OFFSET/FETCH`**,僅在維護舊系統、跨版本相容、或極端效能調校需手動控制求值順序時參考此模式。重構時務必修復「排序唯一性保證」、「動態 SQL 注入」、「ROWCOUNT 殘留」三大硬傷。