SQL Server 分頁預存程序五大寫法效能大比拚：NOT IN vs MAX vs 變數法 vs ROW_NUMBER vs CTE

測試環境建立

CREATE DATABASE data_Test;
USE data_Test;
CREATE TABLE tb_TestTable (
  id INT IDENTITY(1,1) PRIMARY KEY,
  userName NVARCHAR(20) NOT NULL,
  userPWD NVARCHAR(20) NOT NULL,
  userEmail NVARCHAR(40) NULL
);
-- 插入 200 萬筆測試資料
SET IDENTITY_INSERT tb_TestTable ON;
DECLARE @count INT = 1;
WHILE @count <= 2000000 BEGIN
  INSERT INTO tb_TestTable(id, userName, userPWD, userEmail) 
  VALUES(@count, 'admin', 'admin888', 'lli0077@yahoo.com.cn');
  SET @count = @count + 1;
END
SET IDENTITY_INSERT tb_TestTable OFF;

五種分頁寫法實作

方法 1：NOT IN 子查詢（經典但低效）

CREATE PROCEDURE proc_paged_with_notin
  @pageIndex INT, @pageSize INT
AS
BEGIN
  DECLARE @sql NVARCHAR(500), @timediff DATETIME = GETDATE();
  SET @sql = 'SELECT TOP ' + STR(@pageSize) + ' * FROM tb_TestTable 
    WHERE ID NOT IN (SELECT TOP ' + STR(@pageSize*@pageIndex) + ' id FROM tb_TestTable ORDER BY ID ASC) 
    ORDER BY ID';
  EXEC(@sql);
  SELECT DATEDIFF(ms, @timediff, GETDATE()) AS 耗時;
END

缺點：NOT IN 子查詢需掃描大量資料，隨頁碼增加效能急劇下降。

方法 2：MAX(ID) 子查詢（改進版）

CREATE PROCEDURE proc_paged_with_selectMax
  @pageIndex INT, @pageSize INT
AS
BEGIN
  DECLARE @sql NVARCHAR(500), @timediff DATETIME = GETDATE();
  SET @sql = 'SELECT TOP ' + STR(@pageSize) + ' * FROM tb_TestTable 
    WHERE ID > (SELECT MAX(id) FROM (SELECT TOP ' + STR(@pageSize*@pageIndex) + ' id FROM tb_TestTable ORDER BY ID) AS TempTable) 
    ORDER BY ID';
  EXEC(@sql);
  SELECT DATEDIFF(ms, @timediff, GETDATE()) AS 耗時;
END

優點：避免 NOT IN 全掃描，利用 MAX 定位邊界。

方法 3：中間變數法（號稱最快）

CREATE PROCEDURE proc_paged_with_Midvar
  @pageIndex INT, @pageSize INT
AS
BEGIN
  DECLARE @count INT = 0, @ID INT = 0, @timediff DATETIME = GETDATE(), @sql NVARCHAR(500);
  SELECT @count = @count + 1, @ID = CASE WHEN @count <= @pageSize*@pageIndex THEN ID ELSE @ID END 
    FROM tb_TestTable ORDER BY ID;
  SET @sql = 'SELECT TOP ' + STR(@pageSize) + ' * FROM tb_TestTable WHERE ID > ' + STR(@ID);
  EXEC(@sql);
  SELECT DATEDIFF(ms, @timediff, GETDATE()) AS 耗時;
END

原理：單次掃描取得第 N 頁起始 ID，避免子查詢重複排序。

方法 4：ROW_NUMBER() 視窗函數（SQL 2005+ 標準寫法）

CREATE PROCEDURE proc_paged_with_Rownumber
  @pageIndex INT, @pageSize INT
AS
BEGIN
  DECLARE @timediff DATETIME = GETDATE();
  SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS IDRank FROM tb_TestTable
  ) AS IDWithRowNumber
  WHERE IDRank > @pageSize*@pageIndex AND IDRank < @pageSize*(@pageIndex+1);
  SELECT DATEDIFF(ms, @timediff, GETDATE()) AS 耗時;
END

優點：語法標準、可讀性強、支援複雜排序。缺點：需全表排序產生行號，大資料量記憶體壓力大。

方法 5：CTE + ROW_NUMBER 分組分頁

CREATE PROCEDURE proc_CTE
  @pageIndex INT, @pageSize INT
AS
BEGIN
  DECLARE @ctestr NVARCHAR(400), @strSql NVARCHAR(400), @datediff DATETIME = GETDATE();
  SET @ctestr = 'WITH Table_CTE AS (SELECT CEILING((ROW_NUMBER() OVER(ORDER BY ID ASC))/'
    + STR(@pageSize) + ') AS page_num, * FROM tb_TestTable)';
  SET @strSql = @ctestr + ' SELECT * FROM Table_CTE WHERE page_num = ' + STR(@pageIndex);
  EXEC sp_executesql @strSql;
  SELECT DATEDIFF(ms, @datediff, GETDATE()) AS 耗時;
END

效能比較與選型建議

現代最佳實踐：OFFSET-FETCH (SQL 2012+)

SELECT * FROM tb_TestTable 
ORDER BY ID 
OFFSET (@pageIndex-1)*@pageSize ROWS 
FETCH NEXT @pageSize ROWS ONLY;

優勢：語法簡潔、標準化、優化器支援最佳、無需動態 SQL 拼接。

總結

1. SQL Server 2012+ 首選 OFFSET-FETCH
2. SQL 2005-2008 R2 首選 ROW_NUMBER() 或 CTE
3. SQL 2000 或極端效能敏感場景考慮中間變數法
4. 避免 NOT IN 分頁寫法
5. 所有方案前提：分頁欄位必須有索引（最好是叢集索引）