SQL Server 分頁儲存程序實作方案集錦

方案一：使用 TOP + 子查詢

1.1 依當前最大主鍵值分頁

CREATE PROC GetNextPageByMaxId
    @PageSize INT,
    @CurrentMaxId INT
AS
BEGIN
    DECLARE @sql NVARCHAR(200);
    SET @sql = N'SELECT TOP ' + CAST(@PageSize AS VARCHAR(10)) +
               ' * FROM dbo.UserInfo WHERE UserId > ' + CAST(@CurrentMaxId AS VARCHAR(10));
    EXEC sp_executesql @sql;
END;

適用於主鍵連續遞增、無刪除間隙的情境，效能最佳。

1.2 依頁碼與頁大小分頁

CREATE PROC GetPageByNumber
    @PageSize INT,
    @PageNum INT
AS
BEGIN
    DECLARE @Skip BIGINT = @PageSize * (@PageNum - 1);
    DECLARE @sql NVARCHAR(200);
    SET @sql = N'SELECT TOP ' + CAST(@PageSize AS VARCHAR(10)) +
               ' * FROM dbo.UserInfo WHERE UserId NOT IN (' +
               'SELECT TOP ' + CAST(@Skip AS VARCHAR(10)) + ' UserId FROM UserInfo ORDER BY UserId) ' +
               'ORDER BY UserId';
    EXEC sp_executesql @sql;
END;

通用性強，但子查詢 NOT IN (SELECT TOP ...) 在大資料量下效能較差。

方案二：使用 SET ROWCOUNT

CREATE PROC GetPageByRowCount
    @PageSize INT,
    @CurrentMaxId INT
AS
BEGIN
    SET ROWCOUNT @PageSize;
    SELECT * FROM UserInfo WHERE UserId > @CurrentMaxId ORDER BY UserId;
    SET ROWCOUNT 0;
END;

同樣要求主鍵遞增無間隙，語法簡潔。

方案三：SQL Server 2012+ OFFSET/FETCH（推薦）

CREATE PROC GetPageModern
    @PageSize INT,
    @PageNum INT
AS
BEGIN
    SELECT * FROM UserInfo
    ORDER BY UserId
    OFFSET (@PageNum - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

標準語法、計畫穩定、支援任意排序與複雜查詢，為現代專案首選。

方案比較總結

建議

• SQL Server 2012 以上專案直接使用 OFFSET/FETCH。
• 舊版本且主鍵連續遞增，優先用 TOP + 當前最大 ID 或 SET ROWCOUNT。
• 避免在大表使用 NOT IN (SELECT TOP ...)，改用 NOT EXISTS 或連接改寫。