SQL Server 2005 高效分頁新技巧：ROW_NUMBER() 等 Ranking 函數取代臨時表 Identity 法

SQL Server 2000 分頁常用「建立含 Identity 的暫存表 → 插入排序結果 → 依 Identity 篩選頁碼」，效能差、程式碼繁。SQL 2005 引入 ROW_NUMBER()、RANK()、DENSE_RANK()、NTILE() 等視窗函數，單一查詢即可完成高效分頁。

SQL 2000 傳統寫法 (效能痛點)

CREATE PROC northwind_OrdersPaged @PageIndex INT, @PageSize INT
AS
SET ROWCOUNT @PageSize * (@PageIndex + 1);
CREATE TABLE #PageIndex (IndexId INT IDENTITY(1,1), OrderID INT);
INSERT #PageIndex (OrderID) SELECT OrderID FROM Orders ORDER BY OrderID DESC;
SELECT COUNT(OrderID) FROM Orders;
SELECT O.* FROM Orders O, #PageIndex P
WHERE O.OrderID = P.OrderID AND P.IndexId > @PageSize*@PageIndex AND P.IndexId <= @PageSize*(@PageIndex+1)
ORDER BY P.IndexId;

缺點：SET ROWCOUNT 影響後續陳述式、暫存表寫入/讀取 IO、IDENTITY 間隙導致頁碼偏移。

SQL 2005+ 標準分頁寫法 (ROW_NUMBER)

CREATE PROC Orders_Paged_2005
    @PageIndex INT = 0,   -- 0-based
    @PageSize  INT = 20
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH Numbered AS (
        SELECT
            ROW_NUMBER() OVER (ORDER BY OrderID DESC) AS RowNum,
            OrderID, CustomerID, EmployeeID, OrderDate, RequiredDate, ShippedDate,
            ShipVia, Freight, ShipName, ShipAddress, ShipCity, ShipRegion,
            ShipPostalCode, ShipCountry
        FROM dbo.Orders
    )
    SELECT *
    FROM Numbered
    WHERE RowNum BETWEEN @PageIndex * @PageSize + 1 AND (@PageIndex + 1) * @PageSize
    ORDER BY RowNum;

    -- 總筆數 (可分開查詢或加 COUNT() OVER)
    SELECT COUNT(*) FROM dbo.Orders;
END;
GO

進階：OFFSET/FETCH (SQL 2012+ 更簡潔)

SELECT OrderID, CustomerID, OrderDate
FROM dbo.Orders
ORDER BY OrderID DESC
OFFSET @PageIndex * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

• 語法標準化 (ANSI SQL:2008)、最佳化器可直接產生 Top-N Sort 計畫
• 搭配 COUNT(*) OVER() 可單查詢回傳總筆數與分頁資料

Ranking 函數家族快速對照

效能優化關鍵

1. 索引支援 ORDER BY：分頁欄位建立覆蓋索引，避免 Key Lookup
2. 避免 SELECT *：只取需要欄位，減少輸出 IO
3. 深分頁優化：頁碼極大 (如第 10 萬頁) 時，OFFSET 仍需掃描前 N 列 → 改用 Keyset Pagination (游標式) WHERE OrderID < @LastSeenId ORDER BY OrderID DESC LIMIT @PageSize
4. 總筆數分離：COUNT(*) 獨立查詢並快取，避免每頁重複掃描

遷移建議

現有 SQL 2000 分頁預存程序 → 直接改寫為 ROW_NUMBER() CTE 版本，測試通過後部署。若仍在 SQL 2000，可用 TOP + 子查詢模擬 (效能較差) 或升級資料庫相容性等級。

1. 上一篇文章： SQL Server 2005 釋出日期重新確認：微軟承諾夏季上市、開發團隊全力衝刺
2. 下一篇文章： SQL Server 2000 WHILE 迴圈批次插入百萬筆測試資料：純 T-SQL 實作範例

關於SqlServer

SSIS 範本化開發：以專案範本加速 ETL 封裝建立與標準化

ETL 專案中，大量封裝共用相同架構：啟動記錄、結束更新狀態

SQL Server 2000 排序規則衝突解決：跨資料庫/跨欄位 JOIN 錯誤處理

錯誤訊息：「無法解決 equal to 作業的排序規則衝突。

SQL Server 2005 交易式複寫同步架構實作解決讀寫衝突

問題背景 單表 FileDetail 同時承擔高頻寫入（增刪

SQL Server DELETE 語法完整規範：四大語法變體圖文展示與參數詳細說明

11.2.1 DELETE 語法完整定義 DELETE 語句