SQL Server 2008 三種分頁查詢寫法效能實測與比較

測試資料準備

CREATE TABLE TestTable (
    ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FirstName NVARCHAR(100) NULL,
    LastName NVARCHAR(100) NULL,
    Country NVARCHAR(50) NULL,
    Note NVARCHAR(2000) NULL
);

SET IDENTITY_INSERT TestTable ON;
DECLARE @i INT = 1;
WHILE @i <= 20000
BEGIN
    INSERT INTO TestTable (ID, FirstName, LastName, Country, Note)
    VALUES (@i, 'FirstName_XXX', 'LastName_XXX', 'Country_XXX', 'Note_XXX');
    SET @i += 1;
END
SET IDENTITY_INSERT TestTable OFF;

方案一：NOT IN + TOP 巢狀查詢

SELECT TOP 10 *
FROM TestTable
WHERE ID NOT IN (
    SELECT TOP 20 ID FROM TestTable ORDER BY ID
)
ORDER BY ID;

通用寫法：WHERE ID NOT IN (SELECT TOP (頁大小*頁數) ID FROM 表 ORDER BY ID)

缺點： NOT IN 在大量資料下效能差，且若子查詢含 NULL 會導致邏輯錯誤。

方案二：ID > MAX(ID) + TOP 巢狀查詢（推薦）

SELECT TOP 10 *
FROM TestTable
WHERE ID > (
    SELECT MAX(ID) FROM (
        SELECT TOP 20 ID FROM TestTable ORDER BY ID
    ) AS T
)
ORDER BY ID;

通用寫法：WHERE ID > (SELECT MAX(ID) FROM (SELECT TOP (頁大小*頁數) ID FROM 表 ORDER BY ID) AS T)

優點： 善用叢集索引搜尋，避免 NOT IN 掃描，效能最佳。

方案三：游標預存程序 (sp_cursoropen/fetch/close)

CREATE PROCEDURE XiaoZhengGe
    @sqlstr NVARCHAR(4000),
    @currentpage INT,
    @pagesize INT
AS
SET NOCOUNT ON;
DECLARE @P1 INT, @rowcount INT;
EXEC sp_cursoropen @P1 OUTPUT, @sqlstr, @scrollopt=1, @ccopt=1, @rowcount=@rowcount OUTPUT;
SELECT CEILING(1.0*@rowcount/@pagesize) AS 總頁數;
SET @currentpage = (@currentpage-1)*@pagesize + 1;
EXEC sp_cursorfetch @P1, 16, @currentpage, @pagesize;
EXEC sp_cursorclose @P1;
SET NOCOUNT OFF;

優點： 通用性高，無需主鍵、支援任意複雜查詢。
缺點： 效能最差，游標開銷大，不建議高併發場景使用。

效能比較總結 (2 萬筆測試)

進階建議：SQL Server 2012+ 使用 OFFSET/FETCH

SELECT * FROM TestTable
ORDER BY ID
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

此為 ANSI 標準語法，計畫穩定、可讀性佳，升級後請優先採用。

最佳化檢查清單

• 確保分頁欄位有叢集索引或覆蓋索引。
• 避免 SELECT *，僅取必要欄位減少 I/O。
• 若總筆數需顯示，考慮快取 COUNT(*) 或使用近似值。
• 大量資料分頁時，避免深分頁（如第 10000 頁），改用「游標式分頁」或「Keyset Pagination」。