SQL Server 實現 MySQL LIMIT m,n 分頁查詢的三種方案對比

問題背景

MySQL 支持 LIMIT m, n 直接偏移分頁，SQL Server 無原生 LIMIT 語法，需通過 TOP 組合實現等效功能。

方案一：NOT IN 子查詢（經典兼容方案）

-- 查詢第 7-9 條（跳過前 6 條，取 3 條）
SELECT TOP 3 id FROM tablename
WHERE id NOT IN (
    SELECT TOP 6 id FROM tablename
);

通用模板：

SELECT TOP (n-m+1) id FROM tablename
WHERE id NOT IN (
    SELECT TOP (m-1) id FROM tablename
);

缺點：NOT IN 遇 NULL 值失效，大偏移性能差（需掃描前 m 行）。

方案二：ROW_NUMBER() 窗口函數（SQL 2005+ 推薦）

WITH Numbered AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM tablename
)
SELECT id FROM Numbered
WHERE rn BETWEEN 7 AND 9;

優勢：單次掃描、支持任意排序、性能穩定、語義清晰。

方案三：OFFSET FETCH（SQL 2012+ 標準語法）

SELECT id FROM tablename
ORDER BY id
OFFSET 6 ROWS FETCH NEXT 3 ROWS ONLY;

優勢：語法最接近 LIMIT、優化器支持最好、可讀性最強。

性能對比測試（百萬級數據）

最佳實踐建議

1. SQL 2012+：優先使用 OFFSET FETCH
2. SQL 2005-2008 R2：使用 ROW_NUMBER() CTE
3. 舊版本/兼容性要求：NOT IN 或 MAX(id) + TOP 近似分頁
4. 大偏移分頁優化：游標分頁/延遲關聯/覆蓋索引

參數化分頁模板

-- SQL 2012+
DECLARE @pageSize INT = 10, @offset INT = 20;
SELECT id FROM tablename
ORDER BY id
OFFSET @offset ROWS FETCH NEXT @pageSize ROWS ONLY;