大型DELETE（删除大量数据）的一种解决方案

通过执行单条DELETE语句来删除一个大型的数据集会有以下的缺点：

1.DELETE语句的操作要被完整地记录到日志中，这要求在事务日志中要有足够的空间以完成整个事务；

2.在删除操作期间（可能会花费很长时间），从最早打开的事务到当前时间点的所有日志都不能被重写；而且，如果由于某种原因，事务被中断，此前发生的所有操作都将被回滚，这也会花费一些时间；

3.当同时删除许多行时，SQL Server可能会把被删除行上的单一锁升级为排他锁，以阻止DELETE完成之前对目标表的读写访问。

想避免以上的问题，可以参考下面的这种方案的代码：

WHILE 1 = 1
BEGIN
  DELETE TOP (5000) FROM dbo.[SYS_Behavior]
  WHERE ID < 12020942
  
  IF @@rowcount < 5000 BREAK;
END
GO

 

通过类似的方式，也可以把大型UPDATE操作拆分到多个批次中，只要正在修改的属性也是筛选器所使用的属性，代码：

WHILE 1 = 1
BEGIN
  UPDATE TOP (5000) dbo.LargeOrders
    SET custid = 123
  WHERE custid = 55;

  IF @@rowcount < 5000 BREAK;
END
GO

执行效果如下：