SQL Server 分頁查詢最佳實踐：臨時表法實現高效分頁存儲過程

分頁查詢的挑戰與選型

在 SQL Server 2005 之前（無 ROW_NUMBER() 支援），大量資料分頁是棘手問題。經調研多種方案（游標、ID 範圍、子查詢 TOP），發現臨時表 + 身分識別欄位最簡單高效，且 Microsoft 官方 Membership 提供者亦採用此模式。

核心邏輯：三步走策略

1. 計算邊界：依頁碼與頁大小算出起始/結束索引（@PageLowerBound, @PageUpperBound）
2. 建立臨時索引表：CREATE TABLE #PageIndex (IndexId INT IDENTITY(0,1), TargetId INT)，將目標主鍵按序填入
3. 關聯查詢：主表 JOIN 臨時表，依 IndexId 篩選頁面區間

完整預存程序實作

CREATE PROCEDURE usp_GetPagedData
    @PageIndex INT,    -- 0-based 頁碼
    @PageSize INT,     -- 每頁筆數
    @TotalRecords INT OUTPUT  -- 輸出總筆數
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @LowerBound INT = @PageIndex * @PageSize;
    DECLARE @UpperBound INT = @LowerBound + @PageSize - 1;
    
    -- 1. 建立臨時索引表
    CREATE TABLE #TempIndex (
        RowIdx INT IDENTITY(0,1) PRIMARY KEY,
        EntityId INT NOT NULL
    );
    
    -- 2. 填入目標主鍵（可加 WHERE/ORDER BY）
    INSERT INTO #TempIndex (EntityId)
    SELECT EntityId FROM LargeTable
    WHERE Status = 1  -- 篩選條件
    ORDER BY CreateDate DESC;  -- 排序規則
    
    -- 3. 取得總筆數
    SELECT @TotalRecords = @@ROWCOUNT;
    
    -- 4. 關聯查詢目標頁面資料
    SELECT T.* 
    FROM LargeTable T
    INNER JOIN #TempIndex I ON T.EntityId = I.EntityId
    WHERE I.RowIdx BETWEEN @LowerBound AND @UpperBound
    ORDER BY T.CreateDate DESC;
    
    DROP TABLE #TempIndex;
END

C# 資料存取層呼叫範例

public List<Entity> GetPagedList(int pageIndex, int pageSize, out int totalRecords)
{
    var list = new List<Entity>();
    var params = new SqlParameter[]
    {
        new SqlParameter("@PageIndex", pageIndex),
        new SqlParameter("@PageSize", pageSize),
        new SqlParameter("@TotalRecords", SqlDbType.Int) { Direction = ParameterDirection.Output }
    };
    
    using (var reader = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "usp_GetPagedData", params))
    {
        while (reader.Read())
            list.Add(MapEntity(reader));
        reader.Close();
    }
    
    totalRecords = (params[2].Value != DBNull.Value) ? (int)params[2].Value : 0;
    return list;
}

關鍵優化細節

• 僅選主鍵填臨時表：避免全行資料寫入 tempdb，大幅減少 I/O。
• 叢集索引支援：目標表主鍵若為叢集索引，JOIN 效能極佳。
• 參數嗅探防範：必要時加 OPTION (RECOMPILE) 或使用區域變數。
• 總筆數快取：高頻查詢可將 @TotalRecords 緩存，避免重複計算。

版本演進建議

SQL Server 2012+ 建議改用 OFFSET ... FETCH NEXT 標準語法，無需臨時表，計畫穩定度更高。但臨時表法在舊版本與複雜動態排序場景下仍具備不可替代的靈活性。