SQL Server 2000/2005 分頁存儲過程全解析：從基礎到優化與 JOIN 支持

分頁查詢是應用開發中高頻需求。SQL Server 2005 引入 ROW_NUMBER() 窗口函數，使分頁邏輯比 SQL Server 2000 更簡潔高效。本文整理四個版本存儲過程：SQL Server 2005 基礎版、優化版、支持 JOIN 版，以及兼容 SQL Server 2000 的版本，並附贈二分法分頁思路。

版本一：SQL Server 2005 基礎分頁

核心邏輯：動態拼接 SQL → ROW_NUMBER() OVER (ORDER BY ...) 生成行號 → 外層按行號範圍過濾。特點：

• 支持任意表、欄位、排序、WHERE 條件
• 輸出總頁數 @TotalPage、返回總記錄數
• 使用事務包裹，錯誤回滾
• 缺點：每次執行皆重新計算總記錄數，高併發下效能一般

版本二：SQL Server 2005 優化版（V2）

關鍵改進：新增 @totalRecord INT = 0 輸入參數。調用端若已知總記錄數（如緩存、單獨統計表），可直接傳入，省去 COUNT(*) 全表掃描。邏輯如下：

IF @totalRecord <= 0 BEGIN
    -- 僅在未傳入總數時動態計算
    EXEC sp_executesql @sql, N'@totalRecord INT OUTPUT', @totalRecord OUTPUT
END

適用場景：列表頁首次加載計算總數並緩存，翻頁時直接傳入緩存值，大幅降低資料庫壓力。

版本三：SQL Server 2005 JOIN 支持版（V2_Join）

移除 WITH (NOLOCK) 提示，允許 @TableName 傳入複雜查詢（如 TableA a JOIN TableB b ON ...）。參數長度擴展至 150 字符，適配多表關聯分頁。注意：傳入複雜表達式時，@OrderField 需包含表別名（如 a.CreateTime DESC）。

版本四：SQL Server 2000 兼容版

無 ROW_NUMBER()，採用「雙重 TOP + 反向排序」或「臨時表 + IDENTITY」實現。性能較 2005 版遜色，但在老系統維護中仍有價值。核心模式：

-- 思路：取前 N*PageSize 行，再反向取前 PageSize 行
SELECT TOP @PageSize * FROM (
    SELECT TOP @StartRecord * FROM TableName ORDER BY SortField ASC
) t ORDER BY SortField DESC

附贈：二分法分頁思路

針對極大數據集（億級），利用聚簇索引有序特性，通過 WHERE ID > @LastMaxID 避免深分頁掃描。適用於「只看下一頁」「無需跳轉頁碼」的無限滾動場景，效能極佳。

最佳實踐建議

1. 優先使用 SQL Server 2005+ 版本，代碼可維護性與效能雙贏。
2. 高併發列表頁：採用 V2 版，總數緩存 1-5 分鐘，翻頁不重算。
3. 複雜多表查詢：使用 V2_Join 版，將 JOIN 邏輯封裝在視圖或 CTE 中，存儲過程僅負責分頁。
4. 防注入：動態 SQL 必須對 @TableName、@OrderField、@sqlWhere 嚴格白名單驗證，或改用參數化查詢重構。
5. 深分頁優化：頁碼 > 1000 時，建議改用 Keyset Pagination（基於游標 ID）替代 Offset 分頁。

完整存儲過程代碼請參考原文，可直接部署至對應版本 SQL Server 實例中使用。