SQL Server 2005 高效分頁技術：ROW_NUMBER 函數實戰

前言：分頁查詢的演進

在 Web 應用程式中，資料分頁是基本需求。早期 SQL Server 版本缺乏內建分頁支援，開發者常採用臨時表、游標或將全量資料傳至客戶端分頁——效能低落且程式碼複雜。SQL Server 2005 引入 ROW_NUMBER() 視窗函數，配合 CTE（Common Table Expression），讓伺服器端高效分頁成為可能。

核心概念：ROW_NUMBER() 與 CTE

ROW_NUMBER() 函數語法

ROW_NUMBER() OVER (PARTITION BY 分組欄位 ORDER BY 排序欄位)

• PARTITION BY：選擇性，依指定欄位分組，各組獨立編號從 1 開始。
• ORDER BY：必要，決定編號順序。

CTE（公用資料表運算式）

CTE 類似「進階臨時表」，僅在單一陳述式存活，語法清晰、可遞迴，效能優於傳統臨時表。

實戰範例：銷售記錄分頁預存程序

1. 建立測試資料表與資料

IF OBJECT_ID('SalesHistory','U') IS NOT NULL
    DROP TABLE SalesHistory

CREATE TABLE SalesHistory (
    SaleID INT IDENTITY(1,1),
    Product VARCHAR(30),
    SaleDate SMALLDATETIME,
    SalePrice MONEY
)

-- 插入 300 筆測試資料（3 類產品 × 100 筆）
DECLARE @i SMALLINT = 1
WHILE @i <= 100
BEGIN
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('Computer', DATEADD(mm, @i, '1919-03-11'), DATEPART(ms, GETDATE()) + @i + 57)
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('BigScreen', DATEADD(mm, @i, '1927-03-11'), DATEPART(ms, GETDATE()) + @i + 13)
    INSERT INTO SalesHistory (Product, SaleDate, SalePrice)
    VALUES ('PoolTable', DATEADD(mm, @i, '1908-03-11'), DATEPART(ms, GETDATE()) + @i + 29)
    SET @i = @i + 1
END

2. 分頁預存程式碼

CREATE PROCEDURE usp_SalesRecords
    @PageSize INT,      -- 每頁筆數
    @TargetPage INT     -- 目標頁碼（從 1 開始）
AS
BEGIN
    SET NOCOUNT ON

    WITH Sales_CTE AS (
        SELECT
            SaleID,
            Product,
            SaleDate,
            SalePrice,
            CEILING(ROW_NUMBER() OVER (ORDER BY SaleDate ASC) * 1.0 / @PageSize) AS PageNumber
        FROM SalesHistory
    )
    SELECT
        PageNumber, SaleID, Product, SaleDate, SalePrice
    FROM Sales_CTE
    WHERE PageNumber = @TargetPage
    ORDER BY SaleDate ASC
END

3. 執行與結果

-- 每頁 3 筆，取第 2 頁
EXEC usp_SalesRecords @PageSize = 3, @TargetPage = 2

回傳第 4-6 筆依銷售日期排序的記錄，並標示 PageNumber = 2。

關鍵技術解析

分頁邏輯數學原理

CEILING(ROW_NUMBER() / @PageSize) 將連續行號轉換為頁碼：

• 每頁筆數 = @PageSize
• 行號 1~@PageSize → 第 1 頁
• 行號 @PageSize+1 ~ 2×@PageSize → 第 2 頁
• 以此類推

效能優勢

• 單一掃描： 僅掃描基底表一次，無需多次查詢或臨時表。
• 索引友善： 若 SaleDate 有索引，ORDER BY 可避免排序運算子。
• 記憶體效率： CTE 不持久化，僅在查詢期間佔用記憶體。

進階應用與注意事項

動態排序欄位

若排序欄位需動態決定，可搭配 CASE 運算式：

ORDER BY 
    CASE @SortCol WHEN 'Product' THEN Product END,
    CASE @SortCol WHEN 'SaleDate' THEN SaleDate END,
    CASE @SortCol WHEN 'SalePrice' THEN SalePrice END

總筆數與總頁數計算

若前端需顯示分頁導航，可另行查詢 COUNT(*) 或使用 COUNT(*) OVER() 視窗函數（SQL Server 2012+）。

版本相容性

• SQL Server 2005/2008/R2：支援 ROW_NUMBER()、CTE
• SQL Server 2012+：新增 OFFSET ... FETCH 子句，語法更簡潔

結語

ROW_NUMBER() 搭配 CTE 是 SQL Server 2005 時代最優雅的分頁方案，兼顧效能、可讀性與維護性。理解其運作原理，有助於在遷移至新版本時平滑轉換至 OFFSET/FETCH 語法。