SQL Server 2016与2019 DATEADD函数溢出差异问题求助

问题场景 #

两台SQL Server服务器：ServerOne为13.0（2016），ServerTwo为15.0（2019），兼容模式均设为100。

• 执行简单语句SELECT DATEADD(DAY, 2, '9999-12-31 23:59:59')时，两台服务器都会触发“Adding a value to a 'datetime' column caused an overflow”错误，符合datetime类型的取值范围限制。
• 但某特定UPDATE语句仅在ServerTwo（2019）报错：当@VariableA和@VariableB为NULL时，WHERE条件(TableA.Id = @VariableA OR TableA.Id = @VariableB)本应无匹配行、不修改数据，但此时ViewA返回的PartitionedDateTimeField默认值为'9999-12-31 23:59:59'，若TableB.IntField非空，DATEADD运算会触发溢出。
• 额外现象：仅使用单个未赋值变量或直接用NULL替代变量时，不会报错。

差异原因 #

这并非DATEADD函数本身的行为变更，而是SQL Server优化器执行计划的运算顺序差异：

• SQL Server 2016的优化器优先执行(TableA.Id = @VariableA OR TableA.Id = @VariableB)过滤条件，提前排除所有行，因此不会执行OR分支中的DATEADD运算，自然不会触发溢出。
• SQL Server 2019的优化器可能调整了执行顺序，先计算OR分支的条件（包括DATEADD），再应用最终过滤条件。即使最终没有行符合过滤条件，DATEADD的运算已经执行，触发溢出。
• 兼容模式100仅限制部分旧版行为，无法完全约束优化器的执行计划决策逻辑。

解决方案 #

1. 强制优先过滤无匹配行 #

通过CTE或子查询先筛选出符合(TableA.Id = @VariableA OR TableA.Id = @VariableB)的行，再进行后续关联和条件判断，强制优化器先执行过滤：

DECLARE @VariableA INT, 
        @VariableB INT;

WITH FilteredTableA AS (
    SELECT * FROM TableA
    WHERE (TableA.Id = @VariableA OR TableA.Id = @VariableB)
)
UPDATE FilteredTableA
SET BooleanField = 1
FROM FilteredTableA
INNER JOIN ViewA ON FilteredTableA.Id = ViewA.Id
INNER JOIN TableB ON ViewA.Id = TableB.Id
WHERE (ISNULL(TableB.BooleanField, 'FALSE') = 'FALSE'
       OR GETDATE() < DATEADD(DAY, ISNULL(TableB.IntField, 0), ViewA.PartitionedDateTimeField));

2. 安全处理DATEADD溢出 #

方法一：增加前置条件判断

在DATEADD运算前先判断PartitionedDateTimeField是否接近datetime最大值，避免不必要的运算：

DECLARE @VariableA INT, 
        @VariableB INT;

UPDATE TableA
SET BooleanField = 1
FROM TableA
INNER JOIN ViewA ON TableA.Id = ViewA.Id
INNER JOIN TableB ON ViewA.Id = TableB.Id
WHERE (ISNULL(TableB.BooleanField, 'FALSE') = 'FALSE'
       OR (ViewA.PartitionedDateTimeField < '9999-12-31' 
           AND GETDATE() < DATEADD(DAY, ISNULL(TableB.IntField, 0), ViewA.PartitionedDateTimeField)))
  AND (TableA.Id = @VariableA OR TableA.Id = @VariableB);

方法二：使用TRY_DATEADD（SQL Server 2012+支持）

TRY_DATEADD在运算溢出时返回NULL，此时GETDATE() < NULL结果为UNKNOWN，不会满足OR条件，既避免报错也不影响原有逻辑：

DECLARE @VariableA INT, 
        @VariableB INT;

UPDATE TableA
SET BooleanField = 1
FROM TableA
INNER JOIN ViewA ON TableA.Id = ViewA.Id
INNER JOIN TableB ON ViewA.Id = TableB.Id
WHERE (ISNULL(TableB.BooleanField, 'FALSE') = 'FALSE'
       OR GETDATE() < TRY_DATEADD(DAY, ISNULL(TableB.IntField, 0), ViewA.PartitionedDateTimeField))
  AND (TableA.Id = @VariableA OR TableA.Id = @VariableB);

3. 调整ViewA的默认值 #

将ViewA中LEAD函数的默认值改为datetime类型的最大值'9999-12-31 23:59:59.997'，减少溢出触发的概率（若TableB.IntField值过大仍可能溢出，建议结合方法2）：

CREATE VIEW dbo.ViewA
AS 
    SELECT 
        Id,
        DateTimeField,
        LEAD(DateTimeField, 1, '9999-12-31 23:59:59.997') OVER (PARTITION BY Id ORDER BY DateTimeField) AS PartitionedDateTimeField
    FROM 
        dbo.TableX

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