SQL Server 2008 HQL 轉換分頁語句深度解析

HQL 生成的分頁查詢結構

在 SQL Server 2008 中，Hibernate (HQL) 產生的分頁語句通常採用 WITH 子查詢（Common Table Expression, CTE）搭配 ROW_NUMBER() 窗口函數來實現。以下是一個典型範例：

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 hibernaterownr >= 5 AND hibernaterownr < 11;

WITH 子查詢（CTE）的核心價值

WITH ... AS 語法稱為「子查詢因子化」（Subquery Factoring），它允許你在查詢開始時定義一個可重用的 SQL 片段。這帶來兩大優勢：

• 提升可讀性： 將複雜邏輯拆解為具名步驟，使主查詢更清晰。
• 效能優化： 在 UNION ALL 等多次引用相同資料集的場景下，CTE 可確保基礎資料只需掃描一次。優化器若偵測到 CTE 被引用兩次以上，會自動將結果物化到臨時表；若需強制物化，可使用 OPTION (USE HINT('MATERIALIZE_CTE'))（較新版本）或舊版的 materialize 提示。

分頁機制詳解

1. 排序與編號： ROW_NUMBER() OVER (ORDER BY ...) 為結果集分配連續行號，這是分頁的基礎。
2. 篩選窗口： 外層查詢透過 WHERE row_nr BETWEEN @start AND @end 精準截取目標頁面資料。
3. 參數化建議： 實際應用中，應將 5 和 11 替換為參數 @PageSize * (@PageIndex - 1) + 1 與 @PageSize * @PageIndex，防止 SQL 注入並利用執行計畫快取。

效能調優提示

• 確保 ORDER BY 列上有合適索引，避免大量排序溢出到 tempdb。
• 若總筆數統計（COUNT(*)）成為瓶頸，可考慮快取總數或使用近似計數。
• SQL Server 2012+ 可改用 OFFSET ... FETCH 語法，語意更清晰且優化器支援更好。

掌握 CTE 與窗口函數的協作原理，能幫助你在 ORM 框架生成低效 SQL 時進行針對性重寫，顯著提升分頁查詢效能。