SQL Server 通用分頁儲存程序：無游標高效能實作

分頁痛點與演進

大量資料前端分頁會導致網路傳輸暴增、Web 伺服器記憶體壓力大、回應逾時。JDBC 支援原生分頁（LIMIT/OFFSET 推送至資料庫），但傳統 ADO.NET 需自行實作。儲存程序分頁是經典解法。

傳統 TOP 子查詢法缺陷

• 需單一識別欄位，不支援複合主鍵
• 需修改原有 SQL 結構，侵入性強
• 巢狀子查詢深層時效能劣化

通用分頁儲存程序設計

輸入參數

核心邏輯

1. 動態建立全域暫存表 ##TB1516_，注入 IDENTITY(numeric,1,1) AS ID1516 作為分頁鍵
2. 支援兩種輸入：
   　　- 含 FROM 的完整 SQL → 插入 IDENTITY 欄位
   　　- 單純資料表名 → 自動包裝 SELECT *, IDENTITY... INTO ... FROM 表名
3. 依 ID1516 BETWEEN @start AND @end 取得頁面資料
4. 回傳 MAX(ID1516) 為總筆數
5. 清理暫存表

完整程式碼

CREATE PROC execByPage
    @sqlQuery VARCHAR(2000),
    @pageSize INT,
    @pageIndex INT
AS
BEGIN
    SET NOCOUNT ON
    SET ANSI_WARNINGS OFF

    DECLARE @tmpTableName VARCHAR(50) = '##TB1516_' + REPLACE(CAST(NEWID() AS VARCHAR(40)), '-', '')
    DECLARE @subIndex INT = CHARINDEX('from', @sqlQuery)

    IF @subIndex > 0
    BEGIN
        DECLARE @sqlQuery1 VARCHAR(2000) = SUBSTRING(@sqlQuery, 1, @subIndex - 1)
        DECLARE @sqlQuery2 VARCHAR(2000) = SUBSTRING(@sqlQuery, @subIndex, LEN(@sqlQuery))
        SET @sqlQuery = @sqlQuery1 + ',IDENTITY(numeric,1,1) as ID1516 INTO ' + @tmpTableName + ' ' + @sqlQuery2
    END
    ELSE
        SET @sqlQuery = 'SELECT *,IDENTITY(numeric,1,1) as ID1516 INTO ' + @tmpTableName + ' FROM ' + @sqlQuery

    EXEC(@sqlQuery)

    DECLARE @indexStart VARCHAR(20) = CAST((@pageIndex - 1) * @pageSize + 1 AS VARCHAR(20))
    DECLARE @indexEnd VARCHAR(20) = CAST(@pageIndex * @pageSize AS VARCHAR(20))

    EXEC('SELECT * FROM ' + @tmpTableName + ' WHERE ID1516 BETWEEN ' + @indexStart + ' AND ' + @indexEnd)
    EXEC('SELECT MAX(ID1516) AS recordCount FROM ' + @tmpTableName)
    EXEC('DROP TABLE ' + @tmpTableName)
END

使用範例

EXEC execByPage 
    'SELECT OrderID, CustomerID, OrderDate FROM Orders WHERE Status = 1 ORDER BY OrderDate DESC',
    20,  -- 每頁 20 筆
    3    -- 第 3 頁

優缺點分析與現代替代方案

現代最佳實踐（SQL Server 2012+）

SELECT OrderID, CustomerID, OrderDate
FROM Orders
WHERE Status = 1
ORDER BY OrderDate DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY  -- 第 3 頁，每頁 20 筆

原生語法：單掃描、優化器感知、計畫快取友善、支援參數化。新專案首選。

遷移建議

• 舊系統維護期：保留儲存程序，新增參數化防注入
• 新開發/重構：直接使用 OFFSET/FETCH
• 極大資料量（百萬級+）：鍵值分頁或延遲關聯優化