SQL批量删除

Question

我在SQL Server2005中有一个表，其中大约有40亿行。我需要删除大约20亿行。如果我尝试在单个事务中执行此操作，事务日志将被填满并失败。我没有任何额外的空间来使事务日志更大。我认为最好的方法是对delete语句进行批处理(以大约10,000?个批的形式)。

我可能可以使用游标来做这件事，但是这是一种标准的/简单的/聪明的方法吗？

备注:本表没有作为主键的标识列。主键由一个整型外键和一个日期组成。

Answer 1

你可以“一点点”的删除，这也意味着你不会对数据库造成很大的负载。如果您的t-log备份每10分钟运行一次，那么您应该可以在相同的时间间隔内运行一次或两次。您可以将其计划为SQL代理作业

尝试如下所示：

DECLARE @count int
SET @count = 10000

    DELETE  FROM table1 
    WHERE table1id IN (
        SELECT TOP (@count) tableid
        FROM table1
        WHERE x='y'
    )

Answer 2

要删除的行与要保留的行有何区别？这对你有用吗？

while exists (select 1 from your_table where <your_condition>)
delete top(10000) from your_table
where <your_condition>

Answer 3

除了使用截断日志的语句将其放入批处理中之外，您还可能希望尝试以下技巧：

• 除了添加其他条件外，还添加与聚集索引中的第一列匹配的条件
• 删除表中的所有索引，然后在完成删除操作后将它们放回原处(如果这是可能的，并且不会干扰数据库中的任何其他操作)，但保留聚集索引

对于上面的第一点，例如，如果您的PK是集群的，那么找到一个与您想要删除每个批次的行数大致匹配的范围，并使用该范围：

DECLARE @max_id INT, @start_id INT, @end_id INT, @interval INT
SELECT @start_id = MIN(id), @max_id = MAX(id) FROM My_Table
SET @interval = 100000  -- You need to determine the right number here
SET @end_id = @start_id + @interval

WHILE (@start_id <= @max_id)
BEGIN
     DELETE FROM My_Table WHERE id BETWEEN @start_id AND @end_id AND <your criteria>

     SET @start_id = @end_id + 1
     SET @end_id = @end_id + @interval
END