T-SQL 大量資料操作時限制影響行數的技巧與改良

應用場景

• 針對發行集資料表，單次異動過多行可能導致複寫效能崩潰。
• 伺服器記憶體不足，大量資料操作會產生龐大中間結果集，造成執行緩慢。

原始做法：使用 SET ROWCOUNT

SET ROWCOUNT 10000
DELETE FROM temp WHERE OperateTime > @CurrentDate

WHILE @@ROWCOUNT > 1
BEGIN
    DELETE FROM temp WHERE OperateTime > @CurrentDate
END
SET ROWCOUNT 0

關鍵點：

• SET ROWCOUNT N 限制後續陳述式影響的最大行數，設為 0 表示不限制。
• @@ROWCOUNT 回傳上一條陳述式實際影響的行數。

SQL Server 2008 R2 文件警告

下一版本將淘汰 ROWCOUNT，建議改用 TOP 子句。

實務改良版：加入封存與記錄

DECLARE @TempRowCount INT = 0
SET ROWCOUNT 10000

DELETE FROM temp
OUTPUT deleted.* INTO temp_deleted
WHERE DepartureDate < DATEADD(day, -@OldDataExpireDayCount, @CurrentDate)

SET @TempRowCount = @@ROWCOUNT
EXEC PRSDBLOGAffectedRowCount @PackageType, 100, @TempRowCount

WHILE @TempRowCount > 1
BEGIN
    DELETE FROM temp
    OUTPUT deleted.* INTO temp_deleted
    WHERE DepartureDate < DATEADD(day, -@OldDataExpireDayCount, @CurrentDate)

    SET @TempRowCount = @@ROWCOUNT
    EXEC PRSDBLOGAffectedRowCount @PackageType, 100, @TempRowCount
END

SET ROWCOUNT 0

關鍵修正說明

原版在迴圈中直接呼叫記錄預存程序，導致 @@ROWCOUNT 被覆寫為 0（因預存程序內有 SET NOCOUNT ON），造成迴圈無法繼續。改良版先將 @@ROWCOUNT 存入變數 @TempRowCount，再呼叫記錄程序，確保迴圈判斷正確。