您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005/2008/2000 分頁存儲過程完整版：ROW_NUMBER、CTE、三層優化與兼容模式

編輯：關於SqlServer

分頁核心演進脈絡

版本	核心技術	性能特徵	適用場景
SQL 2000	TOP + 子查詢 + 標識列範圍	依賴聚簇索引順序掃描，大偏移量性能急劇下降	存量 2000 系統維護
SQL 2005	ROW_NUMBER() OVER (ORDER BY) + CTE/子查詢	單次掃描賦予行號，優化器可下推 predicate，中等偏移高效	2005/2008/R2 主流生產
SQL 2012+	OFFSET ... FETCH NEXT (ANSI SQL:2008)	語法標準、計劃穩定、支持變量參數化	新開發項目首選

SQL Server 2005/2008 三版本演進 (含 JOIN 支持)

V1：基礎版 (單表、無優化)

CREATE PROC dbo.up_Page2005_V1
    @TableName     VARCHAR(50),
    @Fields        VARCHAR(5000) = '*',
    @OrderField    VARCHAR(5000),
    @SqlWhere      VARCHAR(5000) = NULL,
    @PageSize      INT,
    @PageIndex     INT = 1,
    @TotalPage     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(4000), @TotalRecord INT;

    -- 總記錄數
    SET @Sql = N'SELECT @TotalRecord = COUNT(*) FROM ' + QUOTENAME(@TableName) +
               CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END;
    EXEC sp_executesql @Sql, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;

    SET @TotalPage = CASE WHEN @TotalRecord = 0 THEN 1 ELSE CEILING(@TotalRecord * 1.0 / @PageSize) END;
    IF @PageIndex < 1 SET @PageIndex = 1;
    IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage;

    DECLARE @Start INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @End   INT = @Start + @PageSize - 1;

    SET @Sql = N'SELECT * FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowId, ' + @Fields + N'
                    FROM ' + QUOTENAME(@TableName) +
               CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END +
               N') t WHERE RowId BETWEEN ' + CAST(@Start AS VARCHAR(10)) + N' AND ' + CAST(@End AS VARCHAR(10));

    EXEC sp_executesql @Sql;
    RETURN @TotalRecord;
END;

V2：優化版 (總記錄數緩存、參數化、NOLOCK 提示)

CREATE PROC dbo.up_Page2005_V2
    @TableName     VARCHAR(50),
    @Fields        VARCHAR(5000) = '*',
    @OrderField    VARCHAR(5000),
    @SqlWhere      VARCHAR(5000) = NULL,
    @PageSize      INT,
    @PageIndex     INT = 1,
    @TotalRecord   INT = 0 OUTPUT,  -- 允許外部傳入，避免重複 COUNT
    @TotalPage     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(4000);

    IF @TotalRecord <= 0
    BEGIN
        SET @Sql = N'SELECT @TotalRecord = COUNT(*) FROM ' + QUOTENAME(@TableName) + N' WITH (NOLOCK)' +
                   CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END;
        EXEC sp_executesql @Sql, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;
    END

    SET @TotalPage = CASE WHEN @TotalRecord = 0 THEN 1 ELSE CEILING(@TotalRecord * 1.0 / @PageSize) END;
    IF @PageIndex < 1 SET @PageIndex = 1;
    IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage;

    DECLARE @Start INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @End   INT = @Start + @PageSize - 1;

    SET @Sql = N'SELECT * FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowId, ' + @Fields + N'
                    FROM ' + QUOTENAME(@TableName) + N' WITH (NOLOCK)' +
               CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END +
               N') t WHERE RowId BETWEEN ' + CAST(@Start AS VARCHAR(10)) + N' AND ' + CAST(@End AS VARCHAR(10));

    EXEC sp_executesql @Sql;
    RETURN @TotalRecord;
END;

V3：JOIN/多表版 (支持複雜 FROM 子句、別名、多表排序)

CREATE PROC dbo.up_Page2005_V3_Join
    @FromClause    VARCHAR(8000),   -- 完整 FROM ... JOIN ... 子句
    @Fields        VARCHAR(5000) = '*',
    @OrderField    VARCHAR(5000),   -- 需帶別名，如 t.CreateDate
    @SqlWhere      VARCHAR(5000) = NULL,
    @PageSize      INT,
    @PageIndex     INT = 1,
    @TotalRecord   INT = 0 OUTPUT,
    @TotalPage     INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(4000);

    IF @TotalRecord <= 0
    BEGIN
        SET @Sql = N'SELECT @TotalRecord = COUNT(*) FROM ' + @FromClause + N' WITH (NOLOCK)' +
                   CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END;
        EXEC sp_executesql @Sql, N'@TotalRecord INT OUTPUT', @TotalRecord OUTPUT;
    END

    SET @TotalPage = CASE WHEN @TotalRecord = 0 THEN 1 ELSE CEILING(@TotalRecord * 1.0 / @PageSize) END;
    IF @PageIndex < 1 SET @PageIndex = 1;
    IF @PageIndex > @TotalPage SET @PageIndex = @TotalPage;

    DECLARE @Start INT = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @End   INT = @Start + @PageSize - 1;

    SET @Sql = N'SELECT * FROM (
                    SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowId, ' + @Fields + N'
                    FROM ' + @FromClause + N' WITH (NOLOCK)' +
               CASE WHEN ISNULL(@SqlWhere,'')='' THEN N'' ELSE N' WHERE ' + @SqlWhere END +
               N') t WHERE RowId BETWEEN ' + CAST(@Start AS VARCHAR(10)) + N' AND ' + CAST(@End AS VARCHAR(10));

    EXEC sp_executesql @Sql;
    RETURN @TotalRecord;
END;

SQL Server 2000 兼容模式 (標識列範圍法)

CREATE PROC dbo.up_Page2000
    @QueryStr      NVARCHAR(1000) = 'dbo.Table1',  -- 表/視圖/查詢
    @FdShow        NVARCHAR(2000) = '*',
    @IdentityStr   NVARCHAR(100) = 'ID',           -- 必須為單調遞增/遞減列
    @WhereStr      NVARCHAR(2000) = '1=1',
    @FdOrder       NVARCHAR(100) = 'ASC',          -- ASC/DESC
    @PageSize      INT = 20,
    @PageCurrent   INT = 0,                         -- 0-based
    @RecordCount   INT OUTPUT,
    @ReturnCount   BIT = 0
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @Sql NVARCHAR(4000);

    IF @ReturnCount = 1
    BEGIN
        SET @Sql = N'SELECT @RecordCount = COUNT(*) FROM ' + @QueryStr + N' WHERE ' + @WhereStr;
        EXEC sp_executesql @Sql, N'@RecordCount INT OUTPUT', @RecordCount OUTPUT;
    END

    IF @PageCurrent = 0  -- 首頁
        SET @Sql = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @FdShow +
                   N' FROM ' + @QueryStr + N' WHERE ' + @WhereStr +
                   N' ORDER BY ' + @IdentityStr + N' ' + @FdOrder;
    ELSE
    BEGIN
        IF UPPER(@FdOrder) = 'DESC'
            SET @Sql = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @FdShow +
                       N' FROM ' + @QueryStr + N' WHERE ' + @WhereStr +
                       N' AND ' + @IdentityStr + N' < (SELECT MIN(' + @IdentityStr + N') FROM (SELECT TOP ' +
                       CAST(@PageSize * @PageCurrent AS NVARCHAR(10)) + N' ' + @IdentityStr +
                       N' FROM ' + @QueryStr + N' WHERE ' + @WhereStr + N' ORDER BY ' + @IdentityStr + N' DESC) t) ' +
                       N' ORDER BY ' + @IdentityStr + N' DESC';
        ELSE
            SET @Sql = N'SELECT TOP ' + CAST(@PageSize AS NVARCHAR(10)) + N' ' + @FdShow +
                       N' FROM ' + @QueryStr + N' WHERE ' + @WhereStr +
                       N' AND ' + @IdentityStr + N' > (SELECT MAX(' + @IdentityStr + N') FROM (SELECT TOP ' +
                       CAST(@PageSize * @PageCurrent AS NVARCHAR(10)) + N' ' + @IdentityStr +
                       N' FROM ' + @QueryStr + N' WHERE ' + @WhereStr + N' ORDER BY ' + @IdentityStr + N' ASC) t) ' +
                       N' ORDER BY ' + @IdentityStr + N' ASC';
    END

    EXEC sp_executesql @Sql;
END;

性能優化關鍵點總結

覆蓋索引：`ORDER BY` 列 + `WHERE` 列 + `SELECT` 列 → `INCLUDE` 非鍵列，實現索引掃描無需回表。
避免 SELECT *：明確列出 `@Fields`，減少 I/O 與網絡傳輸。
參數嗅探防護：`OPTION (RECOMPILE)` / `OPTIMIZE FOR (@PageIndex = 1)` / 參數化存儲過程 + 局部變量賦值。
大偏移量優化：`PageIndex > 1000` 時，改用 `Keyset Pagination` (記住上一頁最大/最小 PK，下一頁 `WHERE PK > @LastPK ORDER BY PK LIMIT @PageSize`)。
總數緩存：高併發場景將 `@TotalRecord` 寫入 Redis/Memcached，TTL 30-60s，避免重複 `COUNT(*)` 全表掃描。
NOLOCK 風險提示：可能讀到髒數據/幻讀，核心交易嚴禁，報表/查詢類可評估使用。

調用示例

-- V3 JOIN 版調用
DECLARE @TotalPage INT, @TotalRec INT;
EXEC dbo.up_Page2005_V3_Join
    @FromClause = N'dbo.Orders o JOIN dbo.Customers c ON o.CustID = c.CustID',
    @Fields = N'o.OrderID, o.OrderDate, c.CustName, o.TotalAmount',
    @OrderField = N'o.OrderDate DESC',
    @SqlWhere = N'o.Status = 1 AND c.Region = ''North''',
    @PageSize = 20,
    @PageIndex = 3,
    @TotalRecord = 0,
    @TotalPage = @TotalPage OUTPUT;

上一篇文章： SQL Server 2005+ 索引碎片自動化整理腳本：重建與重組智能判斷
下一篇文章： SQL Server TOP 子句參數化最佳實踐：變數與 sp_executesql 結合

關於SqlServer

SQL Server 索引架構深度解析（一）：聚集 vs 非聚集索引的字典比喻

索引本質：特殊目錄 SQL Server 提供兩種索引：

SQL Server 樹形表非循環遞歸查詢：CTE 遞歸查詢完整指南

前言在處理樹形結構數據（如組織架構、地區劃分、分類目錄）時

SQL Server 2008 表值參數完整指南：從建立類型到實際應用

背景與動機在 SQL Server 2005 及更早版本中

SQL Server 2008 交易記錄檔無法壓縮「Cannot shrink log file 2」終極解法

問題背景交易記錄檔佔用 23GB 空間，實際內容不足 1G

SQL Server 登入帳戶管理全攻略：Enterprise Manager 與 T-SQL 雙軌實務 SQL UPDATE 快速將某欄位全部設為同一值 SQL Server 數據類型詳解：CHAR/VARCHAR/NCHAR/NVARCHAR 存儲差異與字串函數陷阱 SQL Server 7.0 入門教學（五）：預存程序與觸發器開發基礎 SQL Server 索引原理深度解析：從資料頁、IAM 到 B+ 樹結構 IN vs UNION ALL：百萬級代理查詢的 SARG 性能實測 SQL Server 7.0 常見問題彙整：BCP 批量匯出、並發限制、DMO 相容性、DateTime 毫秒差異、STDEV 錯誤、Oracle 連結查詢、遞迴觸發、xp_logininfo 錯誤

欄目導航

SQL Server 2005

SQL Server 2008

關於SqlServer