HQL 轉 SQL Server 2008 分頁查詢：WITH 子句 + ROW_NUMBER() 實戰解析

轉譯後的分頁 SQL 範例

WITH query AS (
    SELECT 
        syusers0.id AS id5,
        syusers0.createdatetime AS createda25_,
        syusers0.modifydatetime AS modifyda35_,
        syusers0.name AS name5,
        syusers0.password AS password5,
        ROW_NUMBER() OVER (ORDER BY syusers0.name DESC) AS hibernaterow_nr
    FROM sypro.syuser syusers0_
    WHERE 1=1
)
SELECT * FROM query 
WHERE hibernaterow_nr >= 5 AND hibernaterow_nr < 11;

WITH 子句（CTE）核心價值

• 可讀性： 將複雜子查詢命名、模組化，主查詢更清晰。
• 效能優化： 同一 CTE 在查詢中被多次參考時，最佳化工具會自動將結果物質化為暫存表，避免重複計算；UNION ALL 各分支共用同一資料來源時效益顯著。
• 遞迴查詢： 支援遞迴 CTE 處理階層式資料。

ROW_NUMBER() 視窗函數分頁原理

1. 依 ORDER BY 子句為結果集排序並產生連續行號。
2. 外層查詢以 WHERE row_nr BETWEEN @start AND @end 切取所需頁面。
3. 適用於 SQL Server 2005+ 版本，為標準分頁寫法。

效能提示

• 確保 ORDER BY 欄位有索引支援，避免大量排序開銷。
• 若 CTE 僅被參考一次，不會物質化，直接內展執行計畫。
• 強制物質化可用 OPTION (USE HINT('MATERIALIZE_CTE'))（較新版本支援）。

適用場景

Hibernate / NHibernate 等 ORM 框架自動產生分頁 SQL 時，常採用此模式。理解其運作機制有助於調整索引、改寫複雜報表查詢。