SQL Server 通用分頁預存程序：動態 SQL 實作、排序支援與效能優化

預存程式碼完整版

CREATE PROCEDURE dbo.Pagination
  @tblName       VARCHAR(255),        -- 表名
  @strGetFields  VARCHAR(1000) = '*', -- 需回傳欄位
  @fldName       VARCHAR(255) = '',   -- 排序欄位
  @PageSize      INT = 10,            -- 頁大小
  @PageIndex     INT = 1,             -- 頁碼（從 1 開始）
  @doCount       BIT = 0,             -- 是否回傳總筆數
  @OrderType     BIT = 0,             -- 0=ASC, 1=DESC
  @strWhere      VARCHAR(1500) = ''   -- WHERE 條件（不含 WHERE 關鍵字）
AS
BEGIN
  SET NOCOUNT ON;
  
  DECLARE @strSQL  VARCHAR(5000),
          @strTmp  VARCHAR(110),
          @strOrder VARCHAR(400);
  
  -- 1. 僅回傳總筆數
  IF @doCount <> 0
  BEGIN
    IF @strWhere <> ''
      SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + '] WHERE ' + @strWhere;
    ELSE
      SET @strSQL = 'SELECT COUNT(*) AS Total FROM [' + @tblName + ']';
    EXEC(@strSQL);
    RETURN;
  END
  
  -- 2. 組裝排序與比較邏輯
  IF @OrderType <> 0
  BEGIN
    SET @strTmp  = '< (SELECT MIN(';
    SET @strOrder = ' ORDER BY [' + @fldName + '] DESC';
  END
  ELSE
  BEGIN
    SET @strTmp  = '> (SELECT MAX(';
    SET @strOrder = ' ORDER BY [' + @fldName + '] ASC';
  END
  
  -- 3. 第一頁：直接 TOP N（最快）
  IF @PageIndex = 1
  BEGIN
    IF @strWhere <> ''
      SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                    ' FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder;
    ELSE
      SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                    ' FROM [' + @tblName + ']' + @strOrder;
    EXEC(@strSQL);
    RETURN;
  END
  
  -- 4. 非第一頁：子查詢跳過前 N 頁
  -- 核心技巧：WHERE 排序欄位 < (SELECT MIN/MAX 排序欄位 FROM 前 N 頁)
  SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                ' FROM [' + @tblName + '] ' +
                'WHERE [' + @fldName + ']' + @strTmp + '[' + @fldName + '] ' +
                'FROM (SELECT TOP ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR) + 
                ' [' + @fldName + '] FROM [' + @tblName + ']' + @strOrder + ') AS tblTmp)' + @strOrder;
  
  IF @strWhere <> ''
    SET @strSQL = 'SELECT TOP ' + CAST(@PageSize AS VARCHAR) + ' ' + @strGetFields + 
                  ' FROM [' + @tblName + '] ' +
                  'WHERE [' + @fldName + ']' + @strTmp + '[' + @fldName + '] ' +
                  'FROM (SELECT TOP ' + CAST((@PageIndex-1)*@PageSize AS VARCHAR) + 
                  ' [' + @fldName + '] FROM [' + @tblName + '] WHERE ' + @strWhere + @strOrder + 
                  ') AS tblTmp) AND ' + @strWhere + @strOrder;
  
  EXEC(@strSQL);
END;
GO

核心邏輯解析

關鍵前提條件

• 排序欄位必須有索引（最佳為叢集索引或覆蓋索引）。
• 排序欄位值應唯一，否則邊界比較會導致資料遺漏或重複。非唯一時需加入 Tie-breaker（如主鍵）：
  WHERE (SortCol, PK) > (SELECT MAX(SortCol), MAX(PK) FROM ...)
• @strWhere 不含 WHERE 關鍵字，直接寫條件（如 status=1 AND type=2）。

現代化替代方案（SQL Server 2012+）

-- OFFSET/FETCH 標準語法，簡潔且優化器支援更好
DECLARE @PageSize INT = 10, @PageIndex INT = 3;
SELECT col1, col2, col3
FROM dbo.MyTable
WHERE status = 1
ORDER BY created_date DESC
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

• 語法標準、可讀性高、計畫穩定。
• 大量資料深分頁時，仍建議配合「Keyset Pagination」（游標式分頁）：
  WHERE created_date < @last_seen_date ORDER BY created_date DESC LIMIT @PageSize

效能優化建議

1. 覆蓋索引：CREATE INDEX IX_Table_SortCol_Includes ON Table(SortCol) INCLUDE (col1, col2, col3); 避免書籤查找。
2. 避免 SELECT *：明確指定 @strGetFields。
3. 統計資料更新：確保優化器正確估算行數。
4. 參數化：動態 SQL 使用 sp_executesql 而非 EXEC(@sql)，重用計畫、防注入。
5. 總筆數快取：@doCount=1 獨立查詢，前端快取總筆數，避免每頁重複 COUNT(*)。

使用範例

-- 第 3 頁，每頁 20 筆，按 id 降序，條件 status=1
EXEC dbo.Pagination 
  @tblName = 'Orders',
  @strGetFields = 'id, customer_id, amount, created_date',
  @fldName = 'id',
  @PageSize = 20,
  @PageIndex = 3,
  @OrderType = 1,
  @strWhere = 'status = 1';

-- 取得總筆數
EXEC dbo.Pagination @tblName = 'Orders', @doCount = 1, @strWhere = 'status = 1';

此分頁預存程序展示了 SQL Server 2000/2005 時代的經典動態 SQL 分頁技巧，現代專案建議直接使用 OFFSET/FETCH 或 Keyset Pagination。