SQL批量删除 [英] SQL Batched Delete

问题描述

我在SQL Server 2005中有一个表，其中有大约40亿行.我需要删除大约20亿行.如果我尝试在单个事务中执行此操作，则事务日志将填满，并且将失败.我没有多余的空间来增加事务日志.我认为最好的方法是批量处理delete语句(约10,000个?).

I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of these rows. If I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to make the transaction log bigger. I assume the best way forward is to batch up the delete statements (in batches of ~ 10,000?).

我可能可以使用光标来执行此操作，但这是一种标准/简单/明智的方式吗?

I can probably do this using a cursor, but is the a standard/easy/clever way of doing this?

P.S.该表没有标识列作为PK. PK由整数外键和日期组成.

P.S. This table does not have an identity column as a PK. The PK is made up of an integer foreign key and a date.

推荐答案

您可以删除"删除的内容，这也意味着不会对数据库造成巨大的负担.如果您的t-log备份每10分钟运行一次，则可以在相同的间隔内运行一次或两次.您可以将其安排为SQL Agent作业

You can 'nibble' the delete's which also means that you don't cause a massive load on the database. If your t-log backups run every 10 mins, then you should be ok to run this once or twice over the same interval. You can schedule it as a SQL Agent job

尝试这样的事情:

DECLARE @count int
SET @count = 10000

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

这篇关于SQL批量删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！