同表父子关系数据按特定顺序批量删除的优化咨询

首先要指出你原来双循环脚本里的明显错误：两个循环的终止逻辑都写错了——当不存在对应行时，你把@MoreRowsToDelete设为1，这会导致死循环，正确应该设为0。

回到你的核心需求：用单循环完成批量删除，无需拆分两种情况。你的CTE+ORDER BY思路是可行的，但需要调整排序逻辑，确保删除顺序符合外键约束要求：先删除那些引用了其他行的子记录（LinkedRequestForMultiChannel_ID IS NOT NULL），再删除没有被引用的父记录，这样就不会触发外键冲突。

优化后的单循环脚本 #

USE [database]

DECLARE @DaysAfterExpiring INT
DECLARE @Issuer_ID INT
DECLARE @DateAfterExpiring DATETIME

SET @DaysAfterExpiring = 35
SET @DateAfterExpiring = DATEADD(day, -(@DaysAfterExpiring), GETUTCDATE())

SELECT @Issuer_ID = [ID] FROM IssuerDescs WHERE [DisplayName] LIKE '%toto%'

DECLARE @RowsDeleted INT = 1

WHILE @RowsDeleted > 0
BEGIN
    -- 先删有引用的子记录，再删无引用的父记录，每次删5000条
    DELETE T
    FROM (
        SELECT TOP (5000) ID
        FROM IssuerRequests 
        WHERE discriminator='IssuerRequest' 
          AND Issuer_ID = @Issuer_ID 
          AND [ExpireDate] < @DateAfterExpiring
        -- 排序逻辑：非NULL的引用记录优先删除，确保父记录最后被删
        ORDER BY CASE WHEN LinkedRequestForMultiChannel_ID IS NOT NULL THEN 0 ELSE 1 END ASC,
                 ID ASC
    ) AS T

    SET @RowsDeleted = @@ROWCOUNT
END

关键说明 #

1. 排序逻辑修正：
   用CASE WHEN明确让有引用的行（LinkedRequestForMultiChannel_ID IS NOT NULL）排在前面，优先被删除。避免了你原来用ISNULL(LinkedRequestForMultiChannel_ID, '') DESC的类型转换问题（INT转字符串会导致排序异常）。
2. 性能优化：
   子查询只选择主键ID，而非*，减少数据传输和处理开销。
3. 循环终止逻辑：
   用@@ROWCOUNT获取每次删除的行数，当删除行数为0时自动终止循环，比EXISTS判断更高效直接。
4. 批量大小调整：
   可以把TOP (5000)改成你需要的批量值，只要数据库性能允许（过大的批量可能导致锁表时间过长，根据实际情况调整）。

额外建议 #

• 如果表数据量极大，可以考虑在删除前创建临时索引，覆盖discriminator, Issuer_ID, ExpireDate, LinkedRequestForMultiChannel_ID这几个查询条件字段，提升子查询的筛选和排序速度，删除完成后再删除临时索引。
• 可以在循环中加入短暂延迟（比如WAITFOR DELAY '00:00:01'），避免长时间占用数据库资源，影响其他业务。

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