SQL Server 窗口函數 ROW_NUMBER() 實現高效分頁：單查詢雙重用

SQL Server 2005+ 引入 ROW_NUMBER() OVER (ORDER BY ...) 窗口函數，配合 CTE 或子查詢，可在單次掃描中同時獲取總記錄數與分頁數據，避免傳統「兩次查詢」或「臨時表」開銷。本文提供標準存儲過程模板與性能分析。

核心邏輯：單語句雙重用

ALTER PROCEDURE usp_GetPagedData
    @PageIndex INT = 1,      -- 當前頁碼 (1-based)
    @PageSize INT = 20,      -- 頁大小
    @TotalPages INT OUTPUT   -- 輸出總頁數
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 1. 計算總頁數 (利用 COUNT(*) OVER() 窗口聚合，單掃描)
    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY Id) AS RowNum,
            COUNT(*) OVER () AS TotalRows  -- 窗口聚合，無需額外掃描
        FROM dbo.StudentsInfo
    )
    SELECT @TotalPages = CEILING(TotalRows * 1.0 / @PageSize)
    FROM Numbered
    WHERE RowNum = 1;  -- 任意取第一行即可獲取 TotalRows
    
    -- 2. 返回分頁數據
    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
        FROM dbo.StudentsInfo
    )
    SELECT * 
    FROM Numbered
    WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
    ORDER BY RowNum;
END;
GO

優化版：合併為單一 CTE (避免雙掃描)

ALTER PROCEDURE usp_GetPagedData_Optimized
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @TotalRows BIGINT OUTPUT,
    @TotalPages INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY Id) AS RowNum,
            COUNT(*) OVER () AS TotalRows
        FROM dbo.StudentsInfo
    )
    SELECT 
        @TotalRows = MAX(TotalRows),
        @TotalPages = CEILING(MAX(TotalRows) * 1.0 / @PageSize)
    FROM Numbered;
    
    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
        FROM dbo.StudentsInfo
    )
    SELECT * 
    FROM Numbered
    WHERE RowNum BETWEEN (@PageIndex - 1) * @PageSize + 1 AND @PageIndex * @PageSize
    ORDER BY RowNum;
END;

注意：上述仍為兩次掃描 (CTE 物化特性)。真正單掃描需應用層處理或 OFFSET/FETCH (2012+)。

SQL Server 2012+ 標準語法：OFFSET / FETCH (推薦)

ALTER PROCEDURE usp_GetPagedData_2012
    @PageIndex INT = 1,
    @PageSize INT = 20,
    @TotalRows BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 總數單獨查詢 (可緩存)
    SELECT @TotalRows = COUNT_BIG(*) FROM dbo.StudentsInfo;
    
    -- 分頁查詢：語法簡潔、優化器友好、支持計劃引導
    SELECT * 
    FROM dbo.StudentsInfo
    ORDER BY Id
    OFFSET (@PageIndex - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

性能對比與選型建議

Keyset Pagination 實現 (深分頁終極方案)

-- 客戶端記住上一頁最後一行的排序鍵值
DECLARE @LastId INT = 10000;  -- 上一頁最大 Id
DECLARE @PageSize INT = 20;

SELECT TOP (@PageSize) *
FROM dbo.StudentsInfo
WHERE Id > @LastId  -- 唯一遞增鍵
ORDER BY Id;

優勢：索引直接定位，無需掃描前 N 行，毫秒級響應無論頁碼多深。
限制：不支持隨機跳頁、排序列必須唯一、向後翻頁のみ。

總結

• SQL 2005/2008：ROW_NUMBER() + CTE 標準化分頁。
• SQL 2012+：OFFSET/FETCH 簡潔標準，配合緩存總數。
• 深分頁/高併發/無限滾動：Keyset Pagination 彻底解決性能痛點。
• 避免 TOP N + NOT IN (SELECT TOP M ...) 反模式，性能隨頁碼線性惡化。