常見 SQL Server 分頁存儲程序寫法比較 (2/3)

方法一：二次 TOP (NOT IN)

CREATE PROCEDURE PagedProc
    @currentpage INT, @pagesize INT
AS
BEGIN
    DECLARE @sqlstr NVARCHAR(4000);
    IF @currentpage = 1
        SET @sqlstr = 'SELECT TOP ' + STR(@pagesize) + ' * FROM r_student ORDER BY Id';
    ELSE
    BEGIN
        SET @sqlstr = 'SELECT TOP ' + STR(@pagesize) + ' * FROM r_student WHERE id NOT IN ';
        SET @sqlstr += '(SELECT TOP ' + STR((@currentpage-1)*@pagesize) + ' id FROM r_student ORDER BY Id)';
    END
    EXEC(@sqlstr);
END

缺點：NOT IN 子查詢效能差；主鍵若非唯一會導致資料遺漏或重複。

方法二：ROW_NUMBER (CTE)

CREATE PROCEDURE PagedProcUseROW_NUMBER
    @currentpage INT, @pagesize INT
AS
BEGIN
    WITH student AS (
        SELECT *, ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber
        FROM r_student
    )
    SELECT * FROM student
    WHERE RowNumber BETWEEN (@currentpage-1)*@pagesize + 1 AND @currentpage*@pagesize;
END

優點：不受排序欄位、重複鍵限制，通用性強，效能良好。
本質：將查詢結果物化為含流水號的暫存結果集，再依區間篩選。