sql server删除大表数据总结

sql server对于大表数据（百万、千万级别）的删除操作一定要注意，尤其是线上的数据更加要谨慎，一不注意可能要hang住整个数据库导致网站或系统不可用。

因为删除操作会记录日志，如果删除的数据比较多可能还会锁表，这时其它的查询如果没有nolock就会阻塞，系统会卡住。

这里我把删除分为两种情况。

一、要删除的数据占用总数据比例少，比如只占10%。

这个时候可以直接用delete，操作。使用分批，一次少量的删除。比如以下是我们一个历史表，要删除200天以前的数据

WHILE 1 = 1
BEGIN
DELETE TOP(5000) FROM His_Order WHERE CreateDate<convert(char(10),GETDATE()-200,120);
IF @@rowcount < 5000
BREAK;
END

一次最多删除5000条。

优点：基本上不影响线上业务，操作简单。

缺点：操作时间长，生成的日志文件可能会很大。

二、要删除的数据占用总数比较多，比如占用60%以上

也就是保留数据少数，大部分数据都是要删除的。

这个时候我们可以采用把要保留的数据筛选出来插入到新表

select * into His_Order2 WHERE CreateDate>=convert(char(10),GETDATE()-200,120)

把新表和当前表重命名

--把不当前表重命名为备份表
EXEC sp_rename 'His_Order', 'His_Order_Bak'
--把要保留的新表重命名为线上可用的表
EXEC sp_rename 'His_Order2', 'His_Order

优点：操作速度快速，效率高

缺点：在重命名表的过程中会有短暂不可用或报错情况，要处理要在转移要保留数据的过过程中，要考虑和处理His_Order中的数据变更（要求不高的系统可以锁表再操作）。