SQL Server 2005 高效分頁查詢三種寫法詳解

SQL Server 2005 引入 ROW_NUMBER() 視窗函數，使分頁查詢變得簡單且高效。本文整理三種常見分頁寫法，適用於不同場景與效能需求。

寫法一：雙層 TOP + ROW_NUMBER（適合大資料量、指定頁碼/頁大小）

DECLARE @Page INT = 3;  -- 第 3 頁
DECLARE @PageSize INT = 10;

SELECT TOP (@PageSize) *
FROM (
    SELECT TOP (@Page * @PageSize)
        ROW_NUMBER() OVER (ORDER BY id) AS RowNum,
        id, username
    FROM Guest
    WHERE username = 'user'
) AS T
WHERE RowNum > ((@Page - 1) * @PageSize);

原理：內層取前 N*頁大小 筆並編號，外層再取最後一頁的頁大小 筆。避免掃描全表，適合深分頁。

寫法二：單層 CTE/子查詢 + BETWEEN（語法簡潔，適合中小資料量）

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum, id, username
    FROM Guest
    WHERE username = 'user'
) AS T
WHERE RowNum BETWEEN 31 AND 60;  -- 第 4 頁 (每頁 10 筆)

特點：可讀性佳，直接指定起迄行號。但內層仍需對符合條件的所有資料排序編號，深分頁效能較寫法一弱。

寫法三：CTE (Common Table Expression) 風格（結構清晰，易維護）

WITH T AS (
    SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNum, id, username
    FROM Guest
    WHERE username = 'user'
)
SELECT * FROM T
WHERE RowNum BETWEEN 31 AND 60;

優勢：邏輯分離，方便複雜查詢嵌套與除錯。執行計畫與寫法二相同。

效能建議

• 淺分頁 (前幾頁)：三種寫法差異不大。
• 深分頁 (第 1000 頁以上)：推薦寫法一，利用 TOP 限制內層掃描範圍。
• 索引支援：確保 ORDER BY 欄位有合適索引（如 id 為叢集索引鍵），避免昂貴的排序運算子。
• 避免 SELECT *：僅選取必要欄位，減少 I/O 與記憶體壓力。