SQL 分頁查詢五大寫法效能實測：NOT IN vs NOT EXISTS vs MAX vs ROW_NUMBER 變體對比

測試環境建立

CREATE DATABASE DBTest; USE DBTest;
CREATE TABLE pagetest (
  id INT IDENTITY(1,1) NOT NULL,
  col01 INT NULL, col02 NVARCHAR(50) NULL, col03 DATETIME NULL
);
-- 插入 1 萬筆測試資料
DECLARE @i INT = 0;
WHILE @i < 10000 BEGIN
  INSERT pagetest VALUES (CAST(RAND()*10000 AS INT), LEFT(NEWID(),10), GETDATE());
  SET @i = @i + 1;
END

測試場景：每頁 50 筆，查詢第 199 頁（9901-9950）。

五種分頁寫法

1. NOT IN + TOP

SELECT TOP 50 * FROM pagetest 
WHERE id NOT IN (SELECT TOP 9900 id FROM pagetest ORDER BY id) 
ORDER BY id;

2. NOT EXISTS

SELECT TOP 50 * FROM pagetest 
WHERE NOT EXISTS (
  SELECT 1 FROM (SELECT TOP 9900 id FROM pagetest ORDER BY id) a 
  WHERE a.id = pagetest.id
) ORDER BY id;

3. MAX(ID) + TOP

SELECT TOP 50 * FROM pagetest 
WHERE id > (SELECT MAX(id) FROM (SELECT TOP 9900 id FROM pagetest ORDER BY id) a) 
ORDER BY id;

4. ROW_NUMBER() 三種變體

-- 4.1 子查詢過濾
SELECT TOP 50 * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY id) rownumber, * FROM pagetest
) a WHERE rownumber > 9900;

-- 4.2 BETWEEN 範圍
SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY id) rownumber, * FROM pagetest
) a WHERE rownumber BETWEEN 9901 AND 9950;

-- 4.3 雙重條件
SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY id) rownumber, * FROM pagetest
) a WHERE rownumber > 9900 AND rownumber < 9951;

5. ROW_NUMBER() 變體：常數欄位排序

SELECT * FROM (
  SELECT ROW_NUMBER() OVER(ORDER BY tempColumn) rownumber, * FROM (
    SELECT TOP 9950 tempColumn = 0, * FROM pagetest WHERE 1=1 ORDER BY id
  ) a
) b WHERE rownumber > 9900;

效能實測結果（10 萬筆資料，第 1990 頁）

結論總結

關鍵發現

1. MAX/TOP 與 ROW_NUMBER() 效能旗鼓相當，前者可移植性更強
2. NOT EXISTS 略優於 NOT IN（避免 NULL 陷阱與全掃描）
3. ROW_NUMBER() 三種寫法效能無顯著差異，優化器會統一重寫
4. 常數欄位變體效能最差，破壞索引排序優勢
5. 所有測試基於 叢集索引鍵 (id) 排序，非索引欄位排序效能將大幅下降

生產級存儲程序封裝（ROW_NUMBER 版）

ALTER PROCEDURE Proc_SqlPageByRownumber
  @tbName VARCHAR(255), @tbGetFields VARCHAR(1000) = '*',
  @OrderfldName VARCHAR(255), @PageSize INT = 20, @PageIndex INT = 1,
  @OrderType BIT = 0, @strWhere VARCHAR(1000) = '', @TotalCount INT OUTPUT
AS
BEGIN
  DECLARE @strOrder VARCHAR(300) = ' ORDER BY ' + @OrderfldName + 
    CASE WHEN @OrderType <> 0 THEN ' DESC' ELSE ' ASC' END;
  
  -- 總筆數
  DECLARE @strSqlCount NVARCHAR(500) = 'SELECT @TotalCout=COUNT(*) FROM ' + @tbName + 
    CASE WHEN @strWhere <> '' THEN ' WHERE 1=1 ' + @strWhere ELSE '' END;
  EXEC sp_executesql @strSqlCount, N'@TotalCout INT OUTPUT', @TotalCount OUTPUT;
  
  -- 分頁查詢
  DECLARE @strSql VARCHAR(5000) = 'SELECT * FROM (SELECT ROW_NUMBER() OVER(' + @strOrder + 
    ') RowNo, ' + @tbGetFields + ' FROM ' + @tbName + ' WHERE 1=1 ' + @strWhere + 
    ') tb WHERE tb.RowNo BETWEEN ' + STR((@PageIndex-1)*@PageSize+1) + ' AND ' + STR(@PageIndex*@PageSize);
  EXEC(@strSql);
  SELECT @TotalCount;
END

多表聯結調用規範

@tbName = "UserInfo u INNER JOIN Department d ON u.DepID = d.ID"
@tbGetFields = "u.ID AS UserID, u.Name, u.Sex, d.ID AS DepID, d.DeptName"
@OrderfldName = "u.ID,ASC|u.Name,DESC"  -- 格式：欄位,排序|欄位,排序
@strWhere = "AND u.DepID = 1 AND u.Status = 1"  -- 每條件前加 AND

MAX/TOP 簡易版（限主鍵名為 id）

CREATE PROC spSqlPageByMaxTop
  @tbName VARCHAR(255), @tbFields VARCHAR(1000), @PageSize INT, @PageIndex INT,
  @strWhere VARCHAR(1000), @StrOrder VARCHAR(255), @Total INT OUTPUT
AS
  -- 總筆數
  DECLARE @strSqlCount NVARCHAR(500) = 'SELECT @TotalCout=COUNT(*) FROM ' + @tbName + 
    CASE WHEN @strWhere <> '' THEN ' WHERE ' + @strWhere ELSE '' END;
  EXEC sp_executesql @strSqlCount, N'@TotalCout INT OUTPUT', @Total OUTPUT;
  
  -- 分頁
  DECLARE @strSql VARCHAR(5000) = 'SELECT TOP ' + STR(@PageSize) + ' * FROM ' + @tbName + 
    ' WHERE id > (SELECT MAX(id) FROM (SELECT TOP ' + STR((@PageIndex-1)*@PageSize) + 
    ' id FROM ' + @tbName + ' ' + @StrOrder + ') a) ' + @StrOrder;
  EXEC(@strSql);

現代最佳實踐

SQL Server 2012+ 直接使用 OFFSET-FETCH，無需動態 SQL、效能最優、語法標準：

SELECT * FROM pagetest 
ORDER BY id 
OFFSET (@PageIndex-1)*@PageSize ROWS 
FETCH NEXT @PageSize ROWS ONLY;