SQL Server 大量更新分批次技巧：SET ROWCOUNT + WHILE 迴圈避免長鎖定與 Log 膨脹

大量資料更新若一次完成，易造成長時間鎖定阻塞並發、交易日誌暴增。本文示範 SET ROWCOUNT 搭配 WHILE 迴圈分批更新的標準模式。

標準分批更新範本

SET ROWCOUNT 1000  -- 每批 1000 列
WHILE (1=1)
BEGIN
    BEGIN TRANSACTION
    UPDATE LargeTable
    SET SomeCol = NewValue,
        MyLastUpdate = GETDATE()  -- 標記已更新
    WHERE MyLastUpdate < '2000-01-01'  -- 僅更新未處理列

    IF @@ROWCOUNT = 0  -- 無列更新，結束
    BEGIN
        COMMIT TRANSACTION
        BREAK
    END
    COMMIT TRANSACTION
    -- 可在此處加 WAITFOR DELAY '00:00:01' 降低衝擊
END
SET ROWCOUNT 0  -- 恢復預設

關鍵機制

• SET ROWCOUNT N：限制後續陳述式影響列數，達 N 列即停止。
• WHERE MyLastUpdate < 'date'：識別未更新列，避免重複處理。
• @@ROWCOUNT = 0：偵測結束條件。
• 每批獨立交易：鎖定時間短、日誌可截斷 (配合 Simple 復原模式或定期備份)。

無法識別已更新列時的替代方案

• 游標逐列處理：資源佔用高、效能差，**不推薦**。
• 新增暫存旗標欄位或追蹤表，配合上述模式。

進階優化建議

1. 批次大小 (1000) 需依據列寬度、索引、並發壓力實測調整 (100~5000 常見)。
2. 搭配 READPAST / NOWAIT 提示減少封鎖 (需評估業務容忍度)。
3. 大量刪除同理適用，將 UPDATE 改為 DELETE。
4. SQL Server 2005+ 可改用 TOP (1000) 語法，語意更清晰：
   WHILE 1=1 BEGIN DELETE TOP (1000) FROM ... WHERE ... IF @@ROWCOUNT=0 BREAK END

總結

SET ROWCOUNT + WHILE 是大量資料異動的經典分批模式，核心在於「小交易、可繼續、可監控」。現代版本優先使用 TOP (N) 寫法，邏輯更直觀。