最近遇到了清理历史数据的需求，整理一下不同场景及对应处理方法
一、 可删除整张表数据
这是最简单的，TRUNCATE / DROP TABLE即可。
二、日志表或历史信息表
这种情况是，代码会不断往表里插入新数据但是并不会去查询，一般是系统异常时开发手动去查。
这种情况可以停业务将原表重命名为bak表，再按原有表结构创建一个新表让系统插入。bak表根据业务要求时间保留，例如三个月，三个月后删除。
-- 重命名
exec sp_rename 'mytab','mytab_bak';
-- 创建新表
select * into mytab from mytab_bak;
--按原表创建索引、约束三、需要实际删除部分数据
这其实才是大部分时候会遇到的情况，对于业务表，通常无法使用前面两种讨巧的方法。
首先需要对表的数据量和需删除的数据量做一个统计，计算删除的比例。
sp_spaceused 'dbo.TEST';
SELECT COUNT(*) from TEST WHERE <删除条件>根据要删除的数据量可以再分为两类
1. 删除表中绝大部分数据
这个绝大部分怎么定义不好量化，所以我们这里就量化为60%。如果删除的数据比例超过60%，就采用下面方法：
- 停业务，新建临时表并插入待保留数据。
对于有alwayson的数据库，事务日志收缩相当麻烦，必须注意insert数据量和产生的事务日志量。如果实在很大，需要分批insert并手动备份事务日志。
-- 创建临时表
select * into mytab_tmp from mytab where xxx;
DBCC SQLPERF(LOGSPACE);--按时间批量插入数据
DECLARE @begindate DATETIME = '2020-02-01';
WHILE @begindate <= '2020-08-24'
BEGIN
INSERT tmp0824 select * from schemalog where logdate>=@begindate and logdate<dateadd(day,7,@begindate)
SET @begindate = dateadd(day,7,@begindate)
END- rename原表
exec sp_rename 'mytab','mytab_bak';- rename新表为原表名
exec sp_rename 'mytab_tmp','mytab';- 按原表新建索引，检查相关的触发器、约束，进行触发器或约束的重命名
- 启动业务，确认无误后（或者保留一段时间），TRUANCATE或DROP bak表
2. 删除表中少部分数据
如果删除条件字段无索引，可以考虑先建上删除完索引后再删除该索引，否则全表扫描执行时间可能非常长。
用小批量分批次删除通常比一次性删除性能要快很多，同时避免锁粒度过大且锁定的时间非常长，和事务日志变得巨大。
到底一次性删除多少数量的记录SQL效率最高呢？这个真没有什么规则计算，个人测试对比过一次删除10000或100000，没有发现什么特别规律。不过一般用10000，在实际操作过程，可以通过做几次实验对比后，选择一个合适的值即可。
案例1
DECLARE @delete_rows INT;
DECLARE @delete_sum_rows INT =0;
DECLARE @row_count INT=100000
WHILE 1 = 1
BEGIN
DELETE TOP ( @row_count )
FROM dbo.[EmployeeDayData]
WHERE WorkDate < CONVERT(DATETIME, '2012-01-01 00:00:00',120);
SELECT @delete_rows = @@ROWCOUNT;
SET @delete_sum_rows +=@delete_rows
IF @delete_rows = 0
BREAK;
END;
SELECT @delete_sum_rows;案例2
DECLARE @r INT;
DECLARE @Delete_ROWS BIGINT;
SET @r = 1;
SET @Delete_ROWS =0
WHILE @r > 0
BEGIN
BEGIN TRANSACTION;
DELETE TOP (10000) -- this will change
mytab
WHERE Remark='今日未入' and Operation_Date<CONVERT(datetime, '2019-05-30',120);
SET @r = @@ROWCOUNT;
SET @Delete_ROWS += @r;
COMMIT TRANSACTION;
PRINT(@Delete_ROWS);
END参考
Break large delete operations into chunks
SQL Server大表如何快速删除数据 - 潇湘隐者 -
