SQL Server 2005 內建分頁查詢支援：ROW_NUMBER() OVER 實作

SQL Server 2005 引入 ROW_NUMBER() OVER (ORDER BY ...) 視窗函數，提供標準、高效的分頁查詢語法，取代舊版複雜的 TOP + NOT IN 或 SET ROWCOUNT 技巧。

標準語法

SELECT *
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY CreditCardID) AS RowNum, *
    FROM Sales.CreditCard
) AS T
WHERE RowNum BETWEEN 1 AND 50;  -- 第 1 頁，每頁 50 筆

通用分頁存儲過程

CREATE PROCEDURE usp_PageQuery_Standard
    @PageNumber INT = 1,
    @PageSize   INT = 20,
    @OrderBy    SYSNAME = 'ID',
    @WhereClause NVARCHAR(MAX) = ''
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @SQL NVARCHAR(MAX), @Start INT = (@PageNumber - 1) * @PageSize + 1,
            @End INT = @PageNumber * @PageSize;

    SET @SQL = N'
    WITH Numbered AS (
        SELECT ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderBy) + N') AS RowNum, *
        FROM TargetTable
        ' + CASE WHEN @WhereClause <> '' THEN N'WHERE ' + @WhereClause ELSE N'' END + N'
    )
    SELECT * FROM Numbered WHERE RowNum BETWEEN ' + CAST(@Start AS NVARCHAR(10)) + N' AND ' + CAST(@End AS NVARCHAR(10)) + N';';

    EXEC sp_executesql @SQL;
END

效能特點

• 最佳化器支援完善：可利用索引順序掃描，避免全表排序。
• 深分頁效能穩定：雖仍需掃描前 N 列，但執行計畫比舊技巧穩定、可預測。
• 標準 SQL：相容 ANSI SQL:2003，遷移至 PostgreSQL/Oracle 只需微調。

進階優化：Keyset Pagination（游標式分頁）

若為無限捲動/下一頁場景，改用鍵值集分頁效能更佳：

-- 第一頁
SELECT TOP 20 * FROM TargetTable ORDER BY ID;

-- 後續頁（記住上一頁最大 ID）
SELECT TOP 20 * FROM TargetTable WHERE ID > @LastMaxID ORDER BY ID;

• 無 OFFSET 掃描成本，索引尋找直接定位。
• 需單調遞增/遞減鍵（如 IDENTITY、時間戳）。
• 不支援任意跳頁，適合「載入更多」介面。

版本演進對照

SQL 2005 起請優先使用 ROW_NUMBER()，代碼更清晰、維護更容易、效能更可預測。