sql server编写archive通用模板脚本实现自动分批删除数据

一、背景和目的
SQL Server是微软的关系型数据库管理系统，使用广泛。随着数据量的增加，库中不少数据已经不再使用，但是不删除的话会影响数据库性能和运行效率。因此，分批删除数据是一种非常必要的操作。但是手动逐条删除非常麻烦，而且容易出错。本文将介绍如何编写SQL Server的archive通用模板脚本，实现自动删除数据的操作。

二、设计方案
1. 批量删除原理：根据一定条件筛选要删除的数据，分批次进行删除。
2. 通用模板脚本：通过定义参数，使得脚本可以用于不同的表和不同的条件。
3. 自动化执行：通过将脚本放置在计划任务中，定期自动运行脚本，实现数据自动删除。

三、具体步骤
1. 创建通用模板脚本

USE [your database name]    --替换成实际的数据库名称
GO
DECLARE @BatchSize INT = 1000   --每次删除记录的数量
DECLARE @TableName VARCHAR(255) = 'your table name'   --替换成实际的表名称
DECLARE @Condition VARCHAR(MAX) = 'your delete condition' --替换成实际的删除条件

DECLARE @TotalRows INT = (SELECT COUNT(*) FROM @TableName WHERE @Condition)   --查询总数据量
DECLARE @DeletedRows INT = 0   --已删除数据量
DECLARE @CurrentBatch INT = 0  --当前批次
DECLARE @BatchCount INT = CEILING(@TotalRows / CAST(@BatchSize AS FLOAT)) --计算需要批次数

WHILE(@CurrentBatch < @BatchCount)
BEGIN
    DELETE TOP(@BatchSize) FROM @TableName WHERE @Condition   --删除记录
    SET @DeletedRows = @DeletedRows + @@ROWCOUNT   --记录已删除数据量
    SET @CurrentBatch = @CurrentBatch + 1  --批次数+1
END

PRINT '已删除 ' + CAST(@DeletedRows AS VARCHAR(20)) + ' 条记录'

说明：
- @BatchSize：每次要删除的记录数
- @TableName：要删除的表名称，可以根据需要更改
- @Condition：要删除的记录的筛选条件，可以根据需要更改

1. 运行测试
   在SSMS中选中要删除的表格，点击“新建查询”按钮，粘贴以上代码后，将“BatchSize”、“TableName”和“Condition”进行替换，然后执行查询。在执行过程中，你可以看到删除进展的消息，如“已删除X条记录”。当执行完成后，查看实际删除情况。

2. 部署和自动执行
   为了定期自动删除数据，我们可以将上述脚本放置在计划任务中执行，以下是一个简单的示例：

3. 打开SQL Server代理管理器，右键点击“工作”文件夹，选择“新建工作”。
4. 在“常规”选项卡中，输入工作名称和描述，并选中“创建工作失败时中止工作步骤”。
5. 在“步骤”选项卡中，点击“新建”按钮，命名步骤和输入要执行的SQL脚本。
6. 在“高级”选项卡中，可以指定工作在何时调度运行。

示例一：
将以上脚本的脚本主体，复制到存储过程中，设置sp为存储过程的名称，设置你需要保留的数据天数为@SaveDays，设置你的表名为@TableName，完成存储过程后，设置如下定时任务：

EXECUTE sp @SaveDays = 30, @TableName = 'your table name'

上述代码表示每月执行一次该存储过程，删除在30天前的“your table name”表里的数据。

示例二：
如果需要每天自动删除30天前的数据，可以直接运行以下脚本：

USE [msdb]
GO

DECLARE @jobId BINARY(16)
EXEC msdb.dbo.sp_add_job @job_name='Daily Database Cleanup', @enabled=1, 
        @job_id = @jobId OUTPUT 

EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name = 'Inventory Cleanup', 
        @subsystem = N'TSQL', @command=N'EXECUTE sp @SaveDays = 30, @TableName='your table name''

EXEC msdb.dbo.sp_add_schedule @job_id=@jobId, 
        @name=N'Daily', @freq_type=4, @freq_interval=1, 
        @active_start_time=10000, @active_end_time=235959

EXEC msdb.dbo.sp_attach_schedule @job_id=@jobId, @schedule_name=N'Daily'

EXEC dbo.sp_add_jobserver @job_id=@jobId, @server_name = N'(local)'

执行上述代码后，可以看到新建了一个名为“Daily Database Cleanup”的计划任务，该任务每日执行一次，并运行在本地服务器上。

四、总结
本文介绍了如何使用SQL Server编写通用模板脚本实现自动分批删除数据的方法。通过定期运行该脚本，可以有效地维护并优化数据库性能，提高查询效率。但是，在运行脚本前，请一定要备份重要的数据，防止误删。