SQL Server 分頁查詢兩大經典實作：NOT IN 子查詢法與 ROW_NUMBER() 視窗函數法

前言

分頁查詢是應用系統最常見的需求。本文介紹兩種經典實作：傳統 NOT IN 子查詢法（適用舊版本）與現代 ROW_NUMBER() 視窗函數法（SQL Server 2005+ 推薦），並提供可直接部署的預存程序範本。

情境設定

• 資料表：Student
• 欄位：StudentNo, LoginPwd, StudentName, Sex, ClassId, Phone, Address, BornDate, Email, isDel
• 需求：每頁顯示 5 筆，支援查詢任意頁碼並回傳總頁數。

方法一：NOT IN 子查詢法（傳統、效能較差）

原理

1. 第 1 頁：SELECT TOP 5 * FROM Student
2. 第 2 頁：SELECT TOP 5 * FROM Student WHERE StudentNo NOT IN (SELECT TOP 10 StudentNo FROM Student)
3. 第 N 頁：排除前 (N-1)×PageSize 筆的主鍵值。

缺點

• NOT IN 遇到 NULL 值會導致邏輯錯誤（結果為空集合）。
• 子查詢每頁需重掃前 N 頁資料，隨頁碼增大效能急劇下降（O(N²)）。
• 難以支援複雜排序與多欄位排序。

方法二：ROW_NUMBER() 視窗函數法（推薦、效能穩定）

核心語法

SELECT ROW_NUMBER() OVER (ORDER BY StudentNo) AS RN, *
FROM Student;

產生連續行號 RN，再以外層查詢篩選 RN BETWEEN (PageIndex-1)*PageSize+1 AND PageIndex*PageSize。

完整分頁預存程序

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'usp_GetPageData')
    DROP PROC usp_GetPageData;
GO

CREATE PROC usp_GetPageData
    @toPage     INT = 0 OUTPUT,  -- 總頁數（輸出參數）
    @pageIndex  INT = 1,         -- 目標頁碼（1-based）
    @pageCount  INT = 5          -- 每頁筆數
AS
BEGIN
    SET NOCOUNT ON;

    /* 1. 取得分頁資料 */
    SELECT 
        temp.StudentNo, temp.LoginPwd, temp.StudentName, temp.Sex,
        temp.ClassId, temp.Phone, temp.Address, temp.BornDate,
        temp.Email, temp.isDel
    FROM (
        SELECT 
            ROW_NUMBER() OVER (ORDER BY StudentNo) AS RN,
            *
        FROM Student
    ) temp
    WHERE temp.RN > (@pageIndex - 1) * @pageCount
      AND temp.RN <= @pageIndex * @pageCount;

    /* 2. 計算總頁數（CEILING 需轉浮點避免整數除法截斷） */
    SET @toPage = CEILING((SELECT COUNT(*) * 1.0 FROM Student) / @pageCount);
END
GO

呼叫範例

DECLARE @totalPages INT;
EXEC usp_GetPageData @toPage = @totalPages OUTPUT, @pageIndex = 3, @pageCount = 5;
PRINT '總頁數：' + CAST(@totalPages AS VARCHAR);

進階優化建議

1. 索引支援排序欄位：ORDER BY 欄位建立索引，避免 Sort 運算子。
2. Keyset Pagination（游標式分頁）：極大資料量（百萬列以上）且頻繁翻深頁時，改用 WHERE PK > @lastSeenPK ORDER BY PK OFFSET 0 ROWS FETCH NEXT @pageSize ROWS ONLY，效能為 O(1)。
3. 避免 SELECT *：僅選取必要欄位，減少 I/O 與網路傳輸。
4. 統計資料維護：定期 UPDATE STATISTICS 確保 Cardinality Estimation 準確。

版本對照表

OFFSET/FETCH 範例（SQL Server 2012+）

DECLARE @PageSize INT = 5, @PageIndex INT = 3;
SELECT StudentNo, StudentName, Sex
FROM Student
ORDER BY StudentNo
OFFSET (@PageIndex - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;