SQL Server参数化批量删除数据未生效问题求助

问题背景 #

SQL Server表中有300万条数据，要删除190万条createdTimestamp早于'2025-01-01'的记录。用带参数的批量删除脚本（Query #1）完全删不掉数据，但把参数硬编码的单批删除语句（Query #2）能正常执行。表中createdTimestamp是datetime2类型，已设主键；小数据量的测试库用同样脚本没问题。

原始脚本 #

Query #1（参数化批量删除，无数据删除）

DECLARE @StartDate DATETIME = GETDATE()
DECLARE @EndDate DATETIME
DECLARE @TillDate DATETIME = '2025-01-01'
DECLARE @RowsAffected BIGINT=0
DECLARE @DeleteBatchSize BIGINT = 10000 --设置批量删除大小为10000，每次删除10000条数据
DECLARE @TotalRecordsDeleted BIGINT = 0

SET @RowsAffected = @DeleteBatchSize

SELECT @RowsAffected,@DeleteBatchSize
WHILE (@RowsAffected = @DeleteBatchSize)
BEGIN
    DELETE TOP(@DeleteBatchSize) FROM [dbo].[Transaction] 
    WHERE [createdTimestamp] < @TillDate

    SET @RowsAffected = @@ROWCOUNT
    SET @TotalRecordsDeleted = @TotalRecordsDeleted + @RowsAffected

    PRINT '已删除总行数 : ' + CAST(@TotalRecordsDeleted AS VARCHAR)
END

SET @EndDate = GETDATE()
PRINT '脚本执行耗时（分钟）: ' + CAST(DATEDIFF(MINUTE,@StartDate,@EndDate) as VARCHAR)

Query #2（硬编码参数，可正常删除）

DELETE TOP(10000) FROM [dbo].[Transaction] 
WHERE [createdTimestamp] < '2025-01-01'

排查原因 #

• 数据类型不匹配导致隐式转换异常：createdTimestamp是datetime2类型，但@TillDate定义成了DATETIME。两种类型比较时，SQL Server会把datetime转成datetime2，但精度差异可能让比较逻辑失效——比如'2025-01-01'存为datetime时是2025-01-01 00:00:00.000，而datetime2字段里可能有2025-01-01 00:00:00.0000000的记录，此时这条记录并不小于datetime类型的变量值。
• 参数嗅探生成错误执行计划：SQL Server可能根据参数初始值的统计信息，生成了认为没有符合条件数据的执行计划，导致循环里的删除操作根本不执行；而硬编码语句会重新生成执行计划，能正确匹配数据。
• 变量赋值精度丢失：把'2025-01-01'赋值给DATETIME变量时，精度处理可能让实际存储值和预期不符，进而导致比较条件不成立。

解决方法 #

方法1：统一数据类型 #

把@TillDate的类型改成datetime2，和字段类型保持一致：

DECLARE @TillDate DATETIME2 = '2025-01-01'

方法2：强制重新编译执行计划 #

在DELETE语句里加OPTION (RECOMPILE)，避免参数嗅探带来的错误执行计划：

DELETE TOP(@DeleteBatchSize) FROM [dbo].[Transaction] 
WHERE [createdTimestamp] < @TillDate
OPTION (RECOMPILE)

方法3：显式指定参数精度 #

如果一定要用DATETIME类型，赋值时显式指定精度，确保比较逻辑一致：

DECLARE @TillDate DATETIME = '2025-01-01 00:00:00.000'

修改后的完整脚本 #

DECLARE @StartDate DATETIME2 = GETDATE()
DECLARE @EndDate DATETIME2
DECLARE @TillDate DATETIME2 = '2025-01-01' -- 统一为datetime2类型
DECLARE @RowsAffected BIGINT=0
DECLARE @DeleteBatchSize BIGINT = 10000
DECLARE @TotalRecordsDeleted BIGINT = 0

SET @RowsAffected = @DeleteBatchSize

WHILE (@RowsAffected = @DeleteBatchSize)
BEGIN
    DELETE TOP(@DeleteBatchSize) FROM [dbo].[Transaction] 
    WHERE [createdTimestamp] < @TillDate
    OPTION (RECOMPILE) -- 避免参数嗅探

    SET @RowsAffected = @@ROWCOUNT
    SET @TotalRecordsDeleted = @TotalRecordsDeleted + @RowsAffected

    PRINT '已删除总行数 : ' + CAST(@TotalRecordsDeleted AS VARCHAR)
END

SET @EndDate = GETDATE()
PRINT '脚本执行耗时（分钟）: ' + CAST(DATEDIFF(MINUTE,@StartDate,@EndDate) as VARCHAR)

内容的提问来源于stack exchange，提问作者Sajith A.K.