T-SQL大大量操作資料的時候限制受影響行數的方法

 T-SQL的威力之一就是大大量操作資料。不過某些情境下需要限制t-sql影響的行數。比如以前藝龍遇到的情境是：對一個發布的表，一次更改太多的行，可能造成發布的崩潰。這次我遇到的情境是伺服器效能不是很好，記憶體不夠大，不限制影響行數的話，記憶體中可能已經容納不下執行sql過程中產生的資料集，執行起來非常慢。

 我們單位的DBA針對這種情況，寫過一個預存程序來應對，核心的代碼如下：

 set rowcount 10000
  delete
  from temp
  WHERE OperateTime > @CurrentDate

  while @@rowcount>1
   delete
   from temp
   WHERE OperateTime > @CurrentDate
 set rowcount 0

 其中用到了兩個關鍵的參數，一個是RowCount，可以設定受影響行數。設為0表示不限。如果上面設了rowcount=10000，下面忘了設rowcount=0，再執行一個select，最多也就返回10000行。另外一個是@@RowCount，表示上一條sql影響的行數。
 
 在sql server 2008 r2的bookonline中，說下一個版本將廢除RowCount，建議改用其他方法，比如使用top參數。
 
 我在最近的這個項目中，對這段代碼做了兩處改動：一是在刪除過程中把被刪除的資料插入到一個存檔表，另外增加了一條日誌：
 set rowcount 10000
  delete
  from temp
  OUTPUT deleted.*
  INTO temp_deleted
  WHERE OperateTime > @CurrentDate
  exec PRSDBLOGAffectedRowCount @PackageType,1350,@@RowCount

  while @@rowcount>1
   delete
   from temp
   OUTPUT deleted.*
   INTO temp_deleted
   WHERE OperateTime > @CurrentDate
   exec PRSDBLOGAffectedRowCount @PackageType,1350,@@RowCount
 set rowcount 0
 
 不過發現那個while迴圈語句沒有執行，因為@@RowCount返回的是上一句sql“exec PRSDBLOGAffectedRowCount @PackageType,1350,@@RowCount”影響的行數。在PRSDBLOGAffectedRowCount中設了SET NOCOUNT ON，返回的@@RowCount都是0，下面的while迴圈永遠不會執行。
 
 最終修改如下：
 
 declare @TempRowCount int = 0
 set rowcount 10000
  DELETE
  FROM temp
  OUTPUT deleted.*
  into temp_deleted
  WHERE DepartureDate < dateadd(day, 0 - @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, 0 - @OldDataExpireDayCount, @CurrentDate)
   
   set @TempRowCount = @@RowCount
   
   exec PRSDBLOGAffectedRowCount @PackageType,100,@TempRowCount
  end
 set rowcount 0