SQL Server 實現 MySQL LIMIT m,n 分頁功能的多種方案

背景

MySQL 支援 LIMIT m, n 跳過 m 筆取 n 筆，但 SQL Server (2005/2008/R2/2012 早期版本) 無此語法，需用 TOP 結合子查詢實現。

方案一：NOT IN 子查詢 (傳統通用做法)

-- 取第 7-9 筆 (跳過 6 筆，取 3 筆)
SELECT TOP 3 id FROM tablename
WHERE id NOT IN (
    SELECT TOP 6 id FROM tablename
)

缺點：大資料量時 NOT IN 效能差，且需確保 id 唯一且有索引。

方案二：雙重 TOP + 排序反轉 (效能較佳)

-- 取第 m+1 到 n 筆 (頁碼從 1 開始，每頁 pageSize 筆)
-- 參數：@pageSize=每頁筆數, @pageIndex=頁碼(1-based)
DECLARE @offset INT = (@pageIndex - 1) * @pageSize;

SELECT TOP (@pageSize) id FROM tablename
WHERE id NOT IN (
    SELECT TOP (@offset) id FROM tablename ORDER BY id
)
ORDER BY id;

方案三：ROW_NUMBER() 視窗函數 (SQL Server 2005+ 推薦)

WITH Numbered AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM tablename
)
SELECT id FROM Numbered
WHERE rn BETWEEN @offset + 1 AND @offset + @pageSize;

優點：語法清晰、效能穩定、支援複雜排序與分組。

方案四：OFFSET FETCH (SQL Server 2012+ 標準語法)

SELECT id FROM tablename
ORDER BY id
OFFSET @offset ROWS
FETCH NEXT @pageSize ROWS ONLY;

最佳選擇：語法最接近 MySQL LIMIT，執行計畫最優化。

效能比較與建議

完整分頁預存程序範例 (相容 2005+)

CREATE PROCEDURE usp_PageQuery
    @pageIndex INT = 1,
    @pageSize INT = 20,
    @totalCount INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    
    -- 取得總筆數
    SELECT @totalCount = COUNT(*) FROM tablename;
    
    -- 計算偏移量
    DECLARE @offset INT = (@pageIndex - 1) * @pageSize;
    
    -- 分頁查詢 (使用 ROW_NUMBER)
    WITH Numbered AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM tablename
    )
    SELECT * FROM Numbered
    WHERE rn BETWEEN @offset + 1 AND @offset + @pageSize;
END