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

SQL Server 2005 高效分頁預存程序：ROW_NUMBER() 實戰詳解

編輯：關於SqlServer

為什麼 SQL 2005 的分頁效能大幅超越 2000？

SQL Server 2005 引入 ROW_NUMBER() OVER (ORDER BY ...) 視窗函數，徹底改變分頁遊戲規則：

單次掃描：只需對資料集掃描一次即可完成排序與編號
最佳化器支援：執行計畫可有效利用索引排序，避免臨時表開銷
實測數據：251 萬筆資料查詢 3 秒內完成，而 SQL 2000 傳統方式需 7 秒

通用分頁預存程序完整程式碼

CREATE PROCEDURE proc_fenye2005
    @tablename    VARCHAR(50),        -- 表名
    @fields       VARCHAR(5000) = '*',-- 查詢欄位，* 表所有
    @orderfield   NVARCHAR(4000),     -- 排序欄位（必填，支援多欄位）
    @sqlwhere     VARCHAR(5000) = NULL, -- WHERE 條件（不含 WHERE 關鍵字）
    @currentpage  INT,                -- 第 N 頁
    @pagesize     INT                 -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRAN;

    DECLARE @rowcount INT, @totalpage INT, @tmpcount INT, @sql NVARCHAR(4000);

    -- 1. 計算總筆數
    IF @sqlwhere = '' OR @sqlwhere IS NULL
        SET @sql = 'SELECT @rowcount = COUNT(*) FROM ' + @tablename;
    ELSE
        SET @sql = 'SELECT @rowcount = COUNT(*) FROM ' + @tablename + ' WHERE ' + @sqlwhere;

    EXEC sp_executesql @sql, N'@rowcount INT OUTPUT', @rowcount OUTPUT;

    SET @totalpage = CEILING((@rowcount + 0.0) / @pagesize);

    -- 2. 邊界檢查
    IF @currentpage < 1 SET @currentpage = 1;
    IF @currentpage > @totalpage SET @currentpage = @totalpage;

    -- 3. 計算分頁範圍
    SET @tmpcount = (@currentpage - 1) * @pagesize + 1;

    -- 4. 建構分頁查詢（核心：ROW_NUMBER 視窗函數）
    IF @sqlwhere = '' OR @sqlwhere IS NULL
        SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderfield + ') AS rowid, ' + @fields + ' FROM ' + @tablename + ') AS t WHERE rowid BETWEEN ' + CAST(@tmpcount AS VARCHAR(50)) + ' AND ';
    ELSE
        SET @sql = 'SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ' + @orderfield + ') AS rowid, ' + @fields + ' FROM ' + @tablename + ' WHERE ' + @sqlwhere + ') AS t WHERE rowid BETWEEN ' + CAST(@tmpcount AS VARCHAR(50)) + ' AND ';

    SET @tmpcount = @tmpcount + @pagesize - 1;
    IF @tmpcount > @rowcount SET @tmpcount = @rowcount;
    SET @sql = @sql + CAST(@tmpcount AS VARCHAR(50));

    -- 5. 執行與交易控制
    EXEC(@sql);
    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRAN;
        SELECT 0 AS currentpage, 0 AS pagesize, 0 AS rowcount, 0 AS totalpage;
    END
    ELSE
    BEGIN
        COMMIT TRAN;
        SELECT @currentpage AS currentpage, @pagesize AS pagesize, @rowcount AS rowcount, @totalpage AS totalpage;
    END
END

呼叫範例與結果說明

-- 查詢 testbigdata 表，第 200 頁，每頁 20 筆，依 id 降序，條件 id%2=0
EXEC proc_fenye2005 'testbigdata', '*', 'id DESC', 'id%2=0', 200, 20;

回傳兩個結果集：

資料集：分頁後的實際資料列
分頁資訊：currentpage、pagesize、rowcount（總筆數）、totalpage（總頁數）

關鍵技術細節解析

技術點	說明
sp_executesql	參數化動態 SQL，重用執行計畫，防注入
ROW_NUMBER() OVER	為排序後的資料列產生連續流水號，支援高效篩選
BETWEEN 範圍	精確鎖定頁碼對應的 rowid 區間
交易控制	確保統計與查詢原子性，異常時回滾
CEILING 計算總頁	加 0.0 強制浮點除法，避免整數除法截斷