您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server ROW_NUMBER() 分頁查詢最佳實踐：預存程序封裝與 Java 呼叫範例

編輯：關於SqlServer

核心邏輯：CTE + ROW_NUMBER() 產生虛擬資料列號

SQL Server 2005+ 支援 ROW_NUMBER() OVER (ORDER BY ...)，配合 CTE（Common Table Expression）即可實現高效分頁，無需臨時表或雙重排序。

標準分頁預存程序範本


CREATE PROCEDURE dbo.usp_GetNewsPaged
    @PageSize  INT,
    @PageIndex INT  -- 0-based：第 0 頁 = 第一頁
AS
BEGIN
    SET NOCOUNT ON;

    ;WITH Numbered AS (
        SELECT 
            *,
            ROW_NUMBER() OVER (ORDER BY Id) AS RowNum
        FROM dbo.News
    )
    SELECT *
    FROM Numbered
    WHERE RowNum BETWEEN (@PageIndex * @PageSize + 1) AND ((@PageIndex + 1) * @PageSize)
    ORDER BY RowNum;
END
GO

關鍵細節解析

0-based 索引：@PageIndex = 0 代表第一頁，符合大多數程式語言習慣（Java、C#、JS）。
BETWEEN 區間：
下限 = @PageIndex * @PageSize + 1
上限 = (@PageIndex + 1) * @PageSize
ORDER BY 必須在 OVER 子句內：
ROW_NUMBER() OVER (ORDER BY Id) ✅
SELECT ... FROM News ORDER BY Id ❌（外層 ORDER BY 不影響 ROW_NUMBER 排序）
RowNum 不可在同層 WHERE 使用：必須包裹在 CTE/子查詢中，因視窗函數在 WHERE 階段尚未計算。

Java (JDBC) 呼叫範例


String sql = "{call usp_GetNewsPaged(?, ?)}";
try (Connection conn = dataSource.getConnection();
     CallableStatement cs = conn.prepareCall(sql)) {
    cs.setInt(1, 20);   // pageSize
    cs.setInt(2, 0);    // pageIndex (第一頁)
    try (ResultSet rs = cs.executeQuery()) {
        while (rs.next()) {
            // 處理欄位...
        }
    }
}

效能優化建議

場景	建議
資料量 < 10 萬、單表	上述 CTE 寫法足夠，配合 `ORDER BY` 欄位建立索引。
資料量 > 100 萬、深分頁（第 1000 頁）	改用 Keyset Pagination（游標式）： `WHERE Id > @LastMaxId ORDER BY Id OFFSET 0 ROWS FETCH NEXT @PageSize ROWS ONLY` (SQL 2012+)
複雜 JOIN、多表排序	將排序鍵與主鍵組合成覆蓋索引，或物化為暫存表再分頁。

常見錯誤與修正


-- ❌ 錯誤：WHERE 直接用 ROW_NUMBER 別名
SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RN FROM News WHERE RN BETWEEN 1 AND 20;

-- ✅ 正確：包裹在 CTE/子查詢
;WITH T AS (SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RN FROM News)
SELECT * FROM T WHERE RN BETWEEN 1 AND 20;