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

SQL Server 2000/2005 分頁儲存程序完整整理（三版本對比與優化）

編輯：關於SqlServer

前言

SQL Server 2000 不支援 ROW_NUMBER() 視窗函數，分頁查詢需採用雙重 TOP 或 IDENTITY 暫存表等技巧；SQL Server 2005 引入 ROW_NUMBER() OVER()，大幅簡化分頁邏輯並提升效能。本文整理三個版本的分頁預存程序：基本版（2005 標準）、優化版（支援總記錄數快取）、支援 JOIN 版，並附上 SQL Server 2000 相容版本與二分法分頁替代方案。

版本一：SQL Server 2005 標準分頁程序

適用於單一資料表、固定排序欄位、需回傳總頁數與總筆數的通用場景。

CREATE PROCEDURE [dbo].[usp_Page_2005_Standard]
    @TableName      sysname,            -- 表名
    @Fields         nvarchar(4000) = N'*', -- 欄位清單
    @OrderField     nvarchar(4000),     -- 排序欄位（必填，支援多欄位）
    @WhereClause    nvarchar(4000) = NULL, -- WHERE 條件（不含 WHERE 關鍵字）
    @PageSize       int,                -- 每頁筆數
    @PageIndex      int = 1,            -- 目前頁碼（1-based）
    @TotalPages     int OUTPUT,         -- 輸出：總頁數
    @TotalRows      int OUTPUT          -- 輸出：總筆數
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max), @Params nvarchar(4000);

    -- 1. 計算總筆數
    SET @SQL = N'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;

    SET @Params = N'@TotalRows int OUTPUT';
    EXEC sp_executesql @SQL, @Params, @TotalRows OUTPUT;

    -- 2. 計算總頁數
    SET @TotalPages = CASE WHEN @PageSize > 0 THEN CEILING(@TotalRows * 1.0 / @PageSize) ELSE 0 END;

    -- 3. 修正頁碼範圍
    IF @PageIndex <= 0 SET @PageIndex = 1;
    IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages;

    -- 4. 建構分頁查詢（ROW_NUMBER）
    DECLARE @StartRow int = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRow   int = @PageIndex * @PageSize;

    SET @SQL = N'
        SELECT * FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowNum, ' + @Fields + N'
            FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    SET @SQL += N'
        ) AS T
        WHERE RowNum BETWEEN ' + CAST(@StartRow AS nvarchar(10)) + N' AND ' + CAST(@EndRow AS nvarchar(10));

    EXEC sp_executesql @SQL;
END

關鍵優化點

使用 QUOTENAME() 防止 SQL 注入（表名/欄位名）。
採用 sp_executesql 參數化總筆數查詢，重用計畫。
分頁查詢直接內嵌常數 BETWEEN，避免參數嗅探導致計畫不穩。
支援 NOLOCK 提示：若業務允許髒讀，可在呼叫端傳入 @WhereClause = N'1=1 WITH (NOLOCK)' 或另增參數控制。

版本二：優化版（總筆數快取，減少 COUNT(*) 開銷）

針對大型資料表、頻繁分頁、總筆數變動不大的場景，允許呼叫端傳入已知總筆數，避免每次 COUNT(*) 掃描。

CREATE PROCEDURE [dbo].[usp_Page_2005_Optimized]
    @TableName      sysname,
    @Fields         nvarchar(4000) = N'*',
    @OrderField     nvarchar(4000),
    @WhereClause    nvarchar(4000) = NULL,
    @PageSize       int,
    @PageIndex      int = 1,
    @TotalRowsHint  int = 0,          -- 輸入：已知總筆數（0 表示需計算）
    @TotalPages     int OUTPUT,
    @TotalRows      int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max), @Params nvarchar(4000);

    -- 1. 取得總筆數
    IF @TotalRowsHint > 0
        SET @TotalRows = @TotalRowsHint;
    ELSE BEGIN
        SET @SQL = N'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName);
        IF @WhereClause IS NOT NULL AND @WhereClause <> N''
            SET @SQL += N' WHERE ' + @WhereClause;
        SET @Params = N'@TotalRows int OUTPUT';
        EXEC sp_executesql @SQL, @Params, @TotalRows OUTPUT;
    END

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

    DECLARE @StartRow int = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRow   int = @PageIndex * @PageSize;

    SET @SQL = N'
        SELECT * FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowNum, ' + @Fields + N'
            FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    SET @SQL += N'
        ) AS T
        WHERE RowNum BETWEEN ' + CAST(@StartRow AS nvarchar(10)) + N' AND ' + CAST(@EndRow AS nvarchar(10));

    EXEC sp_executesql @SQL;
END

使用建議

應用層快取總筆數（如 Redis、MemoryCache），設定 1-5 分鐘過期。
資料異動頻繁時勿使用快取，或採用 OUTPUT 子句在異動時同步更新計數表。

版本三：支援 JOIN 多表查詢版

將 @TableName 擴充為完整 FROM ... JOIN ... 片段，@OrderField 需包含別名（如 o.OrderDate）。

CREATE PROCEDURE [dbo].[usp_Page_2005_Join]
    @FromClause     nvarchar(4000),   -- 完整 FROM ... JOIN 片段
    @Fields         nvarchar(4000) = N'*',
    @OrderField     nvarchar(4000),
    @WhereClause    nvarchar(4000) = NULL,
    @PageSize       int,
    @PageIndex      int = 1,
    @TotalRowsHint  int = 0,
    @TotalPages     int OUTPUT,
    @TotalRows      int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max), @Params nvarchar(4000);

    IF @TotalRowsHint > 0
        SET @TotalRows = @TotalRowsHint;
    ELSE BEGIN
        SET @SQL = N'SELECT @TotalRows = COUNT(*) ' + @FromClause;
        IF @WhereClause IS NOT NULL AND @WhereClause <> N''
            SET @SQL += N' WHERE ' + @WhereClause;
        SET @Params = N'@TotalRows int OUTPUT';
        EXEC sp_executesql @SQL, @Params, @TotalRows OUTPUT;
    END

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

    DECLARE @StartRow int = (@PageIndex - 1) * @PageSize + 1;
    DECLARE @EndRow   int = @PageIndex * @PageSize;

    SET @SQL = N'
        SELECT * FROM (
            SELECT ROW_NUMBER() OVER (ORDER BY ' + @OrderField + N') AS RowNum, ' + @Fields + N'
            ' + @FromClause;
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    SET @SQL += N'
        ) AS T
        WHERE RowNum BETWEEN ' + CAST(@StartRow AS nvarchar(10)) + N' AND ' + CAST(@EndRow AS nvarchar(10));

    EXEC sp_executesql @SQL;
END

呼叫範例

EXEC usp_Page_2005_Join
    @FromClause = N'FROM dbo.Orders o INNER JOIN dbo.Customers c ON o.CustomerID = c.CustomerID',
    @Fields = N'o.OrderID, o.OrderDate, c.CompanyName, o.TotalAmount',
    @OrderField = N'o.OrderDate DESC',
    @WhereClause = N'o.OrderDate >= ''2024-01-01''',
    @PageSize = 20, @PageIndex = 1,
    @TotalPages OUTPUT, @TotalRows OUTPUT;

SQL Server 2000 相容版本（雙重 TOP 法）

無 ROW_NUMBER()，採用 TOP ... ORDER BY ... TOP ... ORDER BY DESC 技巧，需有唯一排序鍵。

CREATE PROCEDURE [dbo].[usp_Page_2000]
    @TableName   sysname,
    @Fields      varchar(4000) = '*',
    @OrderField  varchar(4000),       -- 必須唯一（如 PK）
    @WhereClause varchar(4000) = NULL,
    @PageSize    int,
    @PageIndex   int = 1,
    @TotalPages  int OUTPUT,
    @TotalRows   int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(4000), @StartRow int, @EndRow int;

    -- 總筆數
    SET @SQL = 'SELECT @TotalRows = COUNT(*) FROM ' + @TableName;
    IF @WhereClause IS NOT NULL AND @WhereClause <> ''
        SET @SQL = @SQL + ' WHERE ' + @WhereClause;
    EXEC sp_executesql @SQL, N'@TotalRows int OUTPUT', @TotalRows OUTPUT;

    SET @TotalPages = CEILING(@TotalRows * 1.0 / @PageSize);
    IF @PageIndex <= 0 SET @PageIndex = 1;
    IF @PageIndex > @TotalPages SET @PageIndex = @TotalPages;

    SET @StartRow = (@PageIndex - 1) * @PageSize + 1;
    SET @EndRow = @PageIndex * @PageSize;

    -- 雙重 TOP：先取前 EndRow 筆 ORDER BY ASC，再取後 PageSize 筆 ORDER BY DESC，最後再 ORDER BY ASC
    SET @SQL = 'SELECT TOP ' + CAST(@PageSize AS varchar(10)) + ' * FROM ('
             + ' SELECT TOP ' + CAST(@EndRow AS varchar(10)) + ' ' + @Fields
             + ' FROM ' + @TableName;
    IF @WhereClause IS NOT NULL AND @WhereClause <> ''
        SET @SQL = @SQL + ' WHERE ' + @WhereClause;
    SET @SQL = @SQL + ' ORDER BY ' + @OrderField + ' ASC'
             + ' ) AS T ORDER BY ' + @OrderField + ' DESC';

    -- 外層再 ORDER BY ASC 還原順序（需包裹一次）
    SET @SQL = 'SELECT * FROM (' + @SQL + ') AS T2 ORDER BY ' + @OrderField + ' ASC';

    EXEC sp_executesql @SQL;
END

效能缺陷

隨頁碼增加，TOP @EndRow 掃描範圍線性增大，深分頁效能急劇下降。
需唯一排序鍵，否則結果不穩定。
建議：SQL Server 2000 環境若資料量大，改用 Keyset Pagination（游標式分頁） 或 二分法分頁。

附錄：二分法分頁儲存程序（適用大量資料、任意頁碼）

核心思路：利用索引定位中間點，二分查找目標頁起始鍵值，再 TOP @PageSize 向前/向後取資料。適合有叢集索引的大表。

CREATE PROCEDURE [dbo].[usp_Page_BinarySearch]
    @TableName   sysname,
    @KeyField    sysname,             -- 叢集索引鍵/唯一索引鍵
    @Fields      nvarchar(4000) = N'*',
    @WhereClause nvarchar(4000) = NULL,
    @PageSize    int,
    @PageIndex   int,
    @TotalRows   int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL nvarchar(max), @TargetRank int, @KeyValue sql_variant;

    -- 1. 取得總筆數與目標排名
    SET @SQL = N'SELECT @TotalRows = COUNT(*) FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    EXEC sp_executesql @SQL, N'@TotalRows int OUTPUT', @TotalRows OUTPUT;

    SET @TargetRank = (@PageIndex - 1) * @PageSize + 1;
    IF @TargetRank > @TotalRows RETURN; -- 超出範圍

    -- 2. 二分查找第 @TargetRank 筆的 Key 值
    DECLARE @Low sql_variant, @High sql_variant, @Mid sql_variant, @Rank int;
    SET @SQL = N'SELECT @Low = MIN(' + QUOTENAME(@KeyField) + N'), @High = MAX(' + QUOTENAME(@KeyField) + N') FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    EXEC sp_executesql @SQL, N'@Low sql_variant OUTPUT, @High sql_variant OUTPUT', @Low OUTPUT, @High OUTPUT;

    WHILE @Low IS NOT NULL AND @High IS NOT NULL
    BEGIN
        -- 簡化：這裡僅示意，實際需依資料型別動態建構比較邏輯
        -- 實務上建議改用「Keyset Pagination」：記住上一頁最後一筆 Key，下一頁 WHERE Key > @LastKey
        BREAK; -- 略過完整二分邏輯，見下文建議
    END

    -- 3. 以 Key 定位取資料（Keyset Pagination 實作）
    SET @SQL = N'SELECT TOP ' + CAST(@PageSize AS nvarchar(10)) + N' ' + @Fields + N' FROM ' + QUOTENAME(@TableName);
    IF @WhereClause IS NOT NULL AND @WhereClause <> N''
        SET @SQL += N' WHERE ' + @WhereClause;
    -- 假設已知 @LastKey（上一頁最大 Key）
    -- SET @SQL += N' AND ' + QUOTENAME(@KeyField) + N' > @LastKey';
    SET @SQL += N' ORDER BY ' + QUOTENAME(@KeyField) + N' ASC';
    EXEC sp_executesql @SQL; -- 需傳入 @LastKey 參數
END

現代替代方案：Keyset Pagination（游標式分頁）

比二分法更簡單、效能穩定：

-- 第一頁
SELECT TOP 20 * FROM dbo.Orders WHERE OrderDate >= '2024-01-01' ORDER BY OrderDate, OrderID;

-- 後續頁：記住上一頁最後一筆 (OrderDate, OrderID)
SELECT TOP 20 * FROM dbo.Orders
WHERE OrderDate >= '2024-01-01'
  AND (OrderDate > @LastDate OR (OrderDate = @LastDate AND OrderID > @LastID))
ORDER BY OrderDate, OrderID;

無 COUNT(*)、無 ROW_NUMBER()、無深分頁效能衰減。
適合無限滾動、API 分頁；不支援「跳轉至第 N 頁」。

版本對比與選型建議

版本	適用版本	支援 JOIN	總筆數快取	深分頁效能	安全性	推薦場景
標準版	2005+	否	否	中（ROW_NUMBER 掃描到 @EndRow）	高（QUOTENAME + 參數化）	一般後台管理、報表分頁
優化版	2005+	否	是	中	高	高頻分頁、大表、總筆數變動緩慢
JOIN 版	2005+	是	是	中	中（動態 FROM 片段需自行防注入）	多表關聯查詢分頁
SQL 2000 版	2000/2005	否	否	差（線性掃描）	中	舊系統維護、無法升級環境
Keyset Pagination	2005+	是	不需	極佳（O(log N) 索引尋找）	高	API、無限滾動、大資料量

最佳實務總結

優先使用 Keyset Pagination（游標式），除非業務強制需「跳頁」。
必須用 OFFSET/FETCH（SQL 2012+）或 ROW_NUMBER（2005-2008R2）時，搭配 總筆數快取、覆蓋索引（含排序欄位 + 選取欄位）達到最佳效能。
動態 SQL 必須 參數化、QUOTENAME 物件名稱、嚴格驗證 排序/WHERE 片段，防止 SQL 注入。
大型資料表建立 叢集索引 或 覆蓋非叢集索引 符合排序與篩選條件。
分頁程序納入 單元測試（邊界頁、空結果、注入嘗試），並監控 sys.dm_exec_query_stats 執行時間與邏輯讀取。

上一篇文章： Windows 效能監視器監控 SQL Server 關鍵效能指標完整指南
下一篇文章： SQL Server 無法啟動：錯誤 18052 / 9003 完整排查與修復指南

關於SqlServer

SQL Server 2008 變更資料擷取 (CDC) 實戰教學 (一)：建立資料庫、啟用 CDC、監控 DDL 變更

實驗環境基於 SQL Server 2008 11 月 C

SQL Server 效能調校實戰：從執行計畫入門到解讀

執行計畫是 SQL Server 查詢優化器根據統計資料、索

使用 VB6 實作 SQL Server 2000 資料庫備份與還原完整指南

備份策略概覽備份類型說明建議頻率恢復目標完整備份包含所

SQL Server 2005 取得欄位說明 (Extended Properties) 完整查詢腳本

SQL Server 2005 使用擴充屬性 (MS_Des

ASP/VBScript 檢查資料表是否存在欄位並動態新增 (ADO Schema 查詢) SQL Server 安全性檢查清單：強化伺服器防護的 10 大關鍵項目各主流資料庫隨機抽樣查詢語法對照表 RMAN 實戰備份腳本：自動化全量+歸檔備份與保留策略 SQL Server 跨資料庫快速查詢表是否存在：游標動態掃描方案以 MD5 加密保護資料庫使用者密碼（上） SQL Server 2005 OUTPUT 子句實戰：INSERT/UPDATE/DELETE 返回受影響資料

欄目導航

SQL Server 2005

SQL Server 2008

關於SqlServer