SQL Server逐行删除表数据按固定批次提交的实现问题咨询

问题根源 #

原有代码的逻辑漏洞如下：

• 事务开启语句放在了循环外层，所有删除操作都归属于同一个全局事务，提交逻辑和循环内的删除动作完全脱节
• 提交判断逻辑放在了整个循环结束之后，仅会在所有循环跑完后执行一次提交，自然只有最后一次删除的记录会被持久化
• 计数变量没有区分批次计数和总计数，无法正确触发分批提交的条件

可行实现逻辑（适配SQL Server #temp临时表场景） #

DECLARE @BatchSize INT = 10 -- 每批次提交的删除条数
DECLARE @BatchDeleted INT = 0 -- 单批次已删除条数
DECLARE @TotalDeleted INT = 0 -- 累计总删除条数
DECLARE @TotalToDelete INT -- 总待删除条数

-- 先统计待删除总记录数
SELECT @TotalToDelete = COUNT(*) FROM #temp1 WHERE 【替换为你的删除条件】

WHILE @TotalDeleted < @TotalToDelete
BEGIN
    BEGIN TRANSACTION -- 每个批次开启独立事务
    
    -- 逐行删除，满足逐行删除的要求
    DELETE TOP (1) FROM #temp1 
    WHERE 【替换为你的删除条件】
    
    SET @BatchDeleted = @BatchDeleted + @@ROWCOUNT
    SET @TotalDeleted = @TotalDeleted + @@ROWCOUNT
    
    -- 批次删除数达到阈值、或已删完全部数据时提交事务
    IF @BatchDeleted >= @BatchSize OR @TotalDeleted >= @TotalToDelete
    BEGIN
        COMMIT TRANSACTION
        SET @BatchDeleted = 0 -- 重置批次计数
    END
END

逻辑说明 #

• 每个批次对应独立事务，删除满指定条数就直接提交，不会积压未提交的操作
• 用TOP(1)控制每次删除1条，完全符合逐行删除要求，累计满10条触发提交，总提交次数和需求一致
• 增加总删除条数兜底判断，避免最后不足10条的残留数据无法提交
• 若无需严格逐行删除仅要求分批提交，可将TOP(1)修改为TOP(@BatchSize)，一次删除10条后直接提交，执行性能会更高

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