如何从SQL Server的9亿行数据表中分批次删除超1000天的记录

直接执行你写的delete语句处理9亿行的大表，大概率会因为数据量过大导致事务日志暴涨、锁表超时、执行时间过长，看起来像是没达到预期效果，其实是操作没跑完或者被中断了。下面是针对大表的分批删除方案：

1. 先确保[DateIn]字段有索引（关键） #

如果[DateIn]没有索引，每次删除都会全表扫描9亿行，效率极低。先建索引：

CREATE NONCLUSTERED INDEX IX_table_DateIn ON db.dbo.table ([DateIn])

如果已经有索引，跳过这一步。

2. 循环分批删除数据 #

用WHILE循环每次删除固定行数，直到符合条件的记录删完：

WHILE 1=1
BEGIN
    -- 每次删1万行，可根据服务器性能调整（比如5000、20000）
    DELETE TOP (10000)
    FROM db.dbo.table
    WHERE [DateIn] <= DATEADD(DAY, -1000, GETDATE())

    -- 当影响行数为0时，说明已经删完，退出循环
    IF @@ROWCOUNT = 0
        BREAK;
    
    -- 可选：每次删除后暂停1秒，降低服务器负载
    WAITFOR DELAY '00:00:01'
END

3. 进阶优化（如果表是分区表） #

如果你的表是按[DateIn]分区的，可以用分区切换的方式，直接将旧分区切换到临时表后删除，这是效率最高的方法：

-- 假设旧分区对应的文件组是FG_Old，先创建临时表（结构和原表一致）
CREATE TABLE db.dbo.tmp_old_data (
    -- 复制原表的所有字段结构
) ON FG_Old;

-- 切换分区到临时表
ALTER TABLE db.dbo.table SWITCH PARTITION 1 TO db.dbo.tmp_old_data;

-- 删除临时表
DROP TABLE db.dbo.tmp_old_data;

注意：分区切换需要表结构完全一致，且分区边界要匹配你的1000天条件，适合提前规划了分区的表。

注意事项 #

• 尽量在业务低峰期执行，避免影响线上业务；
• 如果是完整恢复模式，执行前可以考虑切换到简单恢复模式（需要确认业务允许），减少日志占用；
• 监控服务器的CPU、IO和日志文件空间，避免资源耗尽。

内容的提问来源于stack exchange，提问作者jack