SQL Server 表資料逐列處理：表變量 WHILE 迴圈 vs 游標 CURSOR 效能對比

場景

需逐列處理表資料 (如發送通知、呼叫 API、複雜業務邏輯)，集合式操作無法滿足時的兩種主流方案。

方案一：表變量 + WHILE 迴圈 (推薦中小資料量)

版本 A：SET ROWCOUNT (舊式，有副作用)

DECLARE @Temp TABLE (ID INT IDENTITY(1,1), Name VARCHAR(10));
INSERT INTO @Temp VALUES ('a'),('b'),('c'),('d'),('e');

DECLARE @Id INT, @Name VARCHAR(10);
WHILE EXISTS (SELECT 1 FROM @Temp)
BEGIN
    SET ROWCOUNT 1;  -- 影響後續 DELETE/INSERT/UPDATE，慎用
    SELECT @Id = ID, @Name = Name FROM @Temp;
    SET ROWCOUNT 0;
    PRINT 'Name: ' + @Name;
    -- DELETE FROM @Temp WHERE ID = @Id;  -- 若需逐列移除
END

版本 B：TOP 1 + 刪除 (較佳，無 ROWCOUNT 副作用)

WHILE EXISTS (SELECT 1 FROM @Temp)
BEGIN
    SELECT TOP 1 @Id = ID, @Name = Name FROM @Temp;
    DELETE FROM @Temp WHERE ID = @Id;  -- 必須刪除已處理列，否則無限迴圈
    PRINT 'Name: ' + @Name;
END

版本 C：加入 RowNum 欄位 (無需刪除，效能最佳)

DECLARE @Temp TABLE (
    RowNum INT IDENTITY(1,1),  -- 序號
    ID INT,
    Name VARCHAR(10)
);
INSERT INTO @Temp (ID, Name) VALUES (1,'a'),(2,'b'),(3,'c');

DECLARE @MaxRow INT = (SELECT MAX(RowNum) FROM @Temp), @Cur INT = 1;
WHILE @Cur <= @MaxRow
BEGIN
    SELECT @Id = ID, @Name = Name FROM @Temp WHERE RowNum = @Cur;
    PRINT 'Name: ' + @Name;
    SET @Cur += 1;
END

方案二：游標 CURSOR (功能完整、效能較差)

DECLARE @Temp TABLE (ID INT IDENTITY(1,1), Name VARCHAR(10));
INSERT INTO @Temp VALUES ('a'),('b'),('c'),('d'),('e');

DECLARE @Id INT, @Name VARCHAR(10);
DECLARE Cur CURSOR LOCAL FAST_FORWARD FOR  -- 唯讀、順向、最佳化
    SELECT ID, Name FROM @Temp;

OPEN Cur;
FETCH NEXT FROM Cur INTO @Id, @Name;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Name: ' + @Name;
    FETCH NEXT FROM Cur INTO @Id, @Name;
END
CLOSE Cur;
DEALLOCATE Cur;

效能與特性對比

進階游標選項 (若必須用游標)

DECLARE Cur CURSOR 
    LOCAL            -- 區域變數，批次結束自動清理
    FAST_FORWARD     -- FORWARD_ONLY + READ_ONLY + 優化 (最快)
    -- STATIC         -- 複製快照，隔離並發修改
    -- DYNAMIC        -- 即時反映底表變更
    -- KEYSET         -- 鍵集固定，非鍵值變更可見
FOR SELECT ...;

最佳實踐決策樹

1. **能用集合式 SQL (JOIN/APPLY/窗口函數) 解決 → 絕不寫迴圈/游標**
2. **必須逐列 → 表變量 + RowNum WHILE (版本 C) 首選**
3. **需捲動更新、複雜鎖定邏輯 → LOCAL FAST_FORWARD 游標**
4. **大數據量 (10萬+) → 考慮分批處理、Table-Valued Parameter、CLR、Service Broker 非同步**

反模式警示

• 游標預設選項 (GLOBAL、SCROLL、DYNAMIC) → 效能殺手
• WHILE 迴圈中執行單列 INSERT/UPDATE → 改用批次/合併
• 表變量無統計資訊 → 大量資料優化器誤估，改用暫存表 #Temp

「游標是最後手段」非空談。90% 逐列需求可改寫為集合式操作；剩餘 10% 中，表變量 WHILE 多數足夠，游標僅處理極少數複雜邊界情況。