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