SQL Server 分頁存儲程式兩種寫法效能比較與優化建議

背景

在 SQL Server 2000 時代（無 `ROW_NUMBER()`、`OFFSET/FETCH`），分頁查詢是常見挑戰。作者測試 15,000~30,000 筆資料，發現有索引/鍵值時效能差異明顯。

寫法一：SET ROWCOUNT + 子查詢（推薦）

CREATE PROC sp_fixpage @pagesize INT, @destpage INT AS
SET NOCOUNT ON
DECLARE @id INT, @startid INT
SELECT @startid = (@destpage - 1) * @pagesize
SET ROWCOUNT @startid
SELECT @id = id FROM t_member  -- 略過前 N 筆，取得游標位置
SET ROWCOUNT @pagesize
SET NOCOUNT OFF
SELECT * FROM t_member WHERE id > @id ORDER BY id
GO

優點：語法簡潔、利用索引定位、無需臨時表/游標。
缺點：`SET ROWCOUNT` 不影響 `INSERT/UPDATE/DELETE`，未來版本將棄用。

寫法二：SCROLL GAME CURSOR + 臨時表（不推薦）

CREATE PROCEDURE sp_fixpage1 @pagesize INT, @destpage INT AS
SET NOCOUNT ON
CREATE TABLE #myTable ( ... 完整欄位定義 ... )
DECLARE @tempPos INT, @absPos INT, @nowID INT
SET @absPos = CASE WHEN @destpage > 1 THEN (@pagesize * (@destpage - 1) + 1) ELSE 1 END
DECLARE myCursor SCROLL CURSOR FOR SELECT [ID] FROM t_member ORDER BY id
OPEN myCursor
FETCH ABSOLUTE @absPos FROM myCursor INTO @nowID
WHILE (@@FETCH_STATUS = 0) AND (@tempPos <= @pagesize)
BEGIN
    INSERT INTO #myTable SELECT * FROM t_member WHERE [ID] = @nowID
    FETCH NEXT FROM myCursor INTO @nowID
    SET @tempPos = @tempPos + 1
END
CLOSE myCursor; DEALLOCATE myCursor
SELECT * FROM #myTable; DROP TABLE #myTable
GO

缺點：需定義完整臨時表結構、游標開銷大、程式碼冗長、維護困難。

效能測試結論

關鍵因素：資料表是否有鍵值與索引，對兩種寫法效能影響極大。

現代化建議（SQL Server 2012+）

SELECT * FROM t_member ORDER BY id
OFFSET (@pagesize * (@destpage - 1)) ROWS FETCH NEXT @pagesize ROWS ONLY

標準語法、最佳化器支援完善、可讀性最佳。