sqlserver怎么批量删除大量数据

Question

工作库中有一个表记录日志信息，三个月数据量近8百万条，现想只保留一个月历史数据，每个月月初做上上个月的数据清除，所以删除表数据时需要有时间筛选条件，删除时需要锁表，求快速删除表数据方案。

参考网上资料后现自己想出来的方案：

1.分批删除

    SET ROWCOUNT 100000;
    WHILE 1 = 1
    BEGIN
    BEGIN TRAN
    delete from table
      where datediff(MM,tabletime,getdate())>1;
    COMMIT
    IF @@ROWCOUNT = 0
    BREAK;
    END
    SET ROWCOUNT 0;

这个方法缺点也是很慢，很慢很慢。

2.将表数据抽取到临时表，truncate掉正式表(很快)，再将临时表中有用数据抽取回正式表，整个过程包在事物中，这个方法不建议，因为锁表truncate后出错后，事物跳出，表数据可能也会清空 - -

求经常处理大数据的大神给个快速删除的方案。

Answer 1

没有方案。对于已经产生的问题必然要花费额外的时间与精力去解决，所谓的方案只能对未来产生影响。

对于现有数据，时间字段应该被索引，这样就会比较快了，同时不要使用datediff(MM,tabletime,getdate())>1;这种条件，该条件会导致索引失效使执行效率降低，应该直接使用time > '2016-07-01 00:00:00.000'，同时，你可以考虑分批处理。

保留最近一个月的日志通常不是很稳妥，至少也要保留一年的。鉴于数据量很大，可以考虑按周为单位创建多个表，通过逻辑循环创建，这样总是保持最近一年的记录，以后也不会出现类似的问题。

Answer 2

直接对表按照时间分区，然后每个月把之前表空间里面的数据移动出来