SQL Server 2005 分頁查詢三大寫法與效能比較

SQL Server 2005 引入 ROW_NUMBER() 視窗函數，提供標準分頁寫法。本文比較三種常見模式在 8-9 萬筆資料下的效能表現。

寫法一：ROW_NUMBER + 子查詢 (最常見)

SELECT TOP 20 * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, *
    FROM dbo.mem_member
) _myResults
WHERE RowNumber > 10000

或使用 BETWEEN：

SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber, *
    FROM dbo.mem_member
) _myResults
WHERE RowNumber BETWEEN 10000 AND 10020

或搭配 CTE：

WITH OrderedResults AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY Namec) AS RowNumber
    FROM dbo.mem_member
)
SELECT * FROM OrderedResults
WHERE RowNumber BETWEEN 10000 AND 10020

實測：8-9 萬筆資料約需 6 秒，效能不理想 (需掃描前 10,000 列)。

寫法二：暫存表 + 儲存程序 (傳統高效能寫法)

CREATE PROCEDURE GetPage
    @PageLowerBound INT = 10000,
    @PageUpperBound INT = 10020
AS
BEGIN
    CREATE TABLE #PageIndex (
        IndexId INT IDENTITY(1,1) NOT NULL,
        MemberId INT PRIMARY KEY  -- 僅存主鍵
    )
    INSERT INTO #PageIndex (MemberId)
    SELECT MemberId FROM dbo.mem_member ORDER BY Namec

    SELECT m.* FROM dbo.mem_member m
    INNER JOIN #PageIndex p ON m.MemberId = p.MemberId
    WHERE p.IndexId BETWEEN @PageLowerBound AND @PageUpperBound

    DROP TABLE #PageIndex
END

優勢：只對主鍵排序產生序號，再以主鍵 JOIN 取回完整欄位，大幅減少排序與掃描成本。

寫法三：OFFSET/FETCH (SQL Server 2012+)

SELECT * FROM dbo.mem_member
ORDER BY Namec
OFFSET 10000 ROWS FETCH NEXT 20 ROWS ONLY

注意：SQL Server 2005/2008/R2 不支援，2012 起才可用。

效能優化建議

• 確保 ORDER BY 欄位有索引支援。
• 避免 SELECT *，僅查詢必要欄位。
• 大資料量分頁建議使用「鍵值集分頁」 (Keyset Pagination)：WHERE Namec > @LastSeenValue ORDER BY Namec LIMIT 20，效能穩定不隨頁碼遞減。