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

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号