SQL Server 2005 利用 ROW_NUMBER() OVER 實作高效分頁查詢

SQL Server 2005 引入視窗函數 ROW_NUMBER() OVER，提供比舊版 TOP ... NOT IN 或游標更優雅、效能更好的分頁解決方案。本文從基本語法到實戰分頁範例完整說明。

ROW_NUMBER() OVER 基本語法

ROW_NUMBER() OVER (PARTITION BY 分群欄位 ORDER BY 排序欄位) AS RowNum

• PARTITION BY：選擇性，依欄位分群，各群內部編號從 1 開始。
• ORDER BY：必要，決定編號順序。

範例資料

CREATE TABLE Employee (EmpID INT, DeptID INT, Salary DECIMAL(10,2));
INSERT INTO Employee VALUES
(1,10,5500), (2,10,4500), (3,20,1900), (4,20,4800),
(5,40,6500), (6,40,14500), (7,40,44500), (8,50,6500), (9,50,7500);

分群編號展示

SELECT
    EmpID, DeptID, Salary,
    ROW_NUMBER() OVER (PARTITION BY DeptID ORDER BY Salary DESC) AS RankInDept
FROM Employee;
/*
EmpID DeptID Salary RankInDept
7     40     44500  1
6     40     14500  2
5     40     6500   3
...
*/

簡易分頁實作 (每頁 5 筆)

-- 將編號結果作為衍生表
WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum
    FROM Employee
)
SELECT * FROM Numbered WHERE RowNum BETWEEN 1 AND 5;  -- 第 1 頁
SELECT * FROM Numbered WHERE RowNum BETWEEN 6 AND 10; -- 第 2 頁

通用分頁範本 (參數化)

DECLARE @PageSize INT = 20, @PageNum INT = 3;

WITH Numbered AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY SomeColumn) AS RowNum
    FROM YourTable
    WHERE SomeCondition = 1
)
SELECT * FROM Numbered
WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize
ORDER BY RowNum;

多表聯查分頁

同樣原理，將聯查結果包在 CTE 中編號再分頁：

WITH Numbered AS (
    SELECT
        o.OrderID, c.CustomerName, o.OrderDate, o.TotalAmount,
        ROW_NUMBER() OVER (ORDER BY o.OrderDate DESC) AS RowNum
    FROM Orders o
    INNER JOIN Customers c ON c.CustomerID = o.CustomerID
    WHERE o.Status = 'Completed'
)
SELECT * FROM Numbered
WHERE RowNum BETWEEN 21 AND 40; -- 第 2 頁，每頁 20 筆

效能優化建議

• 索引支援 ORDER BY：OVER (ORDER BY ...) 依賴排序，確保排序欄位有索引 (含覆蓋索引) 避免 Sort 運算子。
• 避免 SELECT *：僅選取必要欄位，減少資料傳輸與排序成本。
• 大資料量改用 Keyset Pagination：百萬列以上，ROW_NUMBER 需全量排序，改用 WHERE Key > @LastKey ORDER BY Key LIMIT @PageSize (SQL 2012+ OFFSET/FETCH)。
• 統計資料更新：確保查詢最佳化器有正確基數估計，選擇最佳執行計畫。

版本演進

1. 上一篇文章： SQL Server 2005 安裝錯誤 29503「服務無法啟動」原因分析與解決 (多核 CPU 相容性)
2. 下一篇文章： SQL Server 2005 交易記錄檔清理與壓縮完整指南

SQL Server 2005

SQL Server 2005 四大排名函數深度解析：ROW_NUMBER、RANK、DENSE_RANK、NTILE 與實戰效能案例

一、 四大排名函數速查表 函數用途平局處理編號連續性典型場

SQL Server 設計資料表時關於 NULL 值的最佳實踐建議

在資料庫設計階段，關於欄位是否允許 NULL 值往往會引發團

Windows 7 安裝 SQL Server 2005 Express + SSMS Express 完整避坑指南 (IIS、權限、sa 啟用、遠端連線)

Windows 7 環境安裝 SQL Server 2005

SQL Server 交易與資料庫管理核心概念圖解

核心管理對象 運用 SQL Server 處理資料庫，核心在