[转载]T-SQL大批量操作数据的时候限制受影响行数的方法

[转载]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

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏