千萬級資料通用分頁預存程序 MyPagination 完整實作

前言

面對千萬級資料表，傳統 TOP N 或 ROW_NUMBER() 分頁在深分頁時效能急劇下降。本文提供一個生產環境驗證的通用分頁預存程序 MyPagination，採用動態 SQL + 暫表身份欄位策略，支援任意表、任意條件、任意排序，並輸出總筆數。

預存程序完整程式碼

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[MyPagination]
(
    @tblName       varchar(255),      -- 表名
    @strGetFields  varchar(1000) = '*', -- 需回傳欄位，* 表全部
    @PageSize      int = 10,           -- 頁大小
    @PageIndex     int = 1,            -- 頁碼（從 1 起）
    @doCount       bit = 0,            -- 非 0 則回傳總筆數
    @strOrderBy    varchar(500) = '',  -- 排序欄位，勿加 ORDER BY，格式：Field1 DESC, Field2 ASC
    @strWhere      varchar(1500) = '', -- 查詢條件，勿加 WHERE
    @RecordCount   int OUTPUT          -- 輸出總筆數
)
AS
BEGIN
    DECLARE @strSQL varchar(5000) = ''
    DECLARE @strOrder varchar(400) = ''
    SET @RecordCount = 0

    /*----- 1. 統計總筆數（選擇性） -----*/
    IF (@doCount != 0)
    BEGIN
        DECLARE @sWhere varchar(2000) = ''
        IF (@strWhere != '') SET @sWhere = ' WHERE ' + @strWhere
        
        -- 使用暫表 tmpTable 儲存總數，避免重複掃描
        SET @strSQL = 'IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(''[dbo].[tmpTable]'') AND OBJECTPROPERTY(id, ''IsUserTable'') = 1) '
        SET @strSQL = @strSQL + ' UPDATE tmpTable SET Total = (SELECT COUNT(*) FROM [' + @tblName + '] ' + @sWhere + ') '
        SET @strSQL = @strSQL + ' ELSE SELECT COUNT(*) AS Total INTO tmpTable FROM [' + @tblName + '] ' + @sWhere
        
        EXEC (@strSQL)
        SELECT @RecordCount = Total FROM tmpTable
        EXEC ('DROP TABLE tmpTable')
    END

    PRINT @RecordCount

    /*----- 2. 組裝排序子句 -----*/
    IF (@strOrderBy != '') SET @strOrder = ' ORDER BY ' + @strOrderBy

    /*----- 3. 第一頁直接 TOP N，效能最佳 -----*/
    IF (@PageIndex = 1)
    BEGIN
        IF (@strWhere != '')
            SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder
        ELSE
            SET @strSQL = 'SELECT TOP ' + STR(@PageSize) + ' ' + @strGetFields + ' FROM [' + @tblName + ']' + @strOrder
    END
    /*----- 4. 非第一頁：暫表 + IDENTITY 分頁 -----*/
    ELSE
    BEGIN
        -- 步驟 4.1 取前 N*PageSize 筆寫入暫表 #tmpTable，自動產生流水號 IID
        SET @strSQL = 'SELECT TOP ' + STR(@PageIndex * @PageSize) + ' IDENTITY(int,1,1) AS IID, ' + @strGetFields + ' INTO #tmpTable FROM [' + @tblName + ']'
        
        IF (@strWhere != '')
            SET @strSQL = @strSQL + ' WHERE ' + @strWhere + @strOrder
        ELSE
            SET @strSQL = @strSQL + @strOrder

        -- 步驟 4.2 從暫表取目標頁區間，並刪除暫表
        SET @strSQL = @strSQL + ' SELECT ' + @strGetFields + ' FROM #tmpTable WHERE IID > ' + STR((@PageIndex - 1) * @PageSize) + ' DROP TABLE #tmpTable'
    END

    PRINT @strSQL  -- 除錯用，可移除
    EXEC (@strSQL)
END
GO

參數說明與使用範例

呼叫範例

DECLARE @PageCount int, @RecordCount bigint
EXEC MyPagination 
    @tblName = 'dbo.Orders',
    @strGetFields = 'OrderID, CustomerID, OrderDate, TotalAmount',
    @strWhere = 'Status = 1',
    @strOrderBy = 'OrderDate DESC',
    @PageSize = 20,
    @PageIndex = 3,
    @doCount = 1,
    @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS TotalRecords

設計亮點與效能考量

• 第一頁直取 TOP N：避免暫表開銷，最快路徑
• 深分頁用 IDENTITY 暫表：單次掃描前 N 頁資料，避免游標或遞迴 CTE 開銷
• 動態 SQL 靈活度高：支援任意表、欄位、條件、排序組合
• 自增欄位處理：文件註明需 (fldName + 0) AS fldName 避免 INSERT 衝突
• 總數統計可選：@doCount=0 時跳過 COUNT(*)，大幅降低高併發壓力

適用版本與遷移建議

原設計針對 SQL Server 2000/2005。SQL Server 2012+ 建議改用標準 OFFSET ... FETCH 語法，計畫穩定且無需動態 SQL；但本方案在舊版本或需動態組裝查詢時仍具參考價值。