Upsert存储过程在目标字段为Null时失效的问题排查与解决

问题描述 #

我需要修复下面的存储过程，因为当目标表字段值为Null时它无法正常工作。当EmailAddress为Null时，trg.EmailAddress <> src.EmailAddress的判断结果始终为False，导致Upsert脚本的更新部分无法执行。请问添加Null判断（如trg.EmailAddress <> src.EmailAddress OR trg.EmailAddress IS NULL）是否为高效的解决方式？

当前表类型和存储过程定义：

CREATE TYPE [dbo].[UserType] AS TABLE(
    [FirstName] [varchar](500) NULL,
    [LastName] [varchar](500) NULL,
    [FullName] [varchar](500) NULL,
    [EmailAddress] [varchar](500) NULL
)
GO

CREATE PROCEDURE [dbo].[uspMergeUser]
(
    @User [dbo].[UserType] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    MERGE dbo.User AS trg
    USING @User AS src
        ON (trg.UserID = src.UserID )
    WHEN MATCHED AND 
        (
            trg.FirstName <> src.FirstName
            OR trg.LastName <> src.LastName
            OR trg.FullName <> src.FullName
            OR trg.EmailAddress <> src.EmailAddress
        )
   THEN
        UPDATE SET 
            trg.[FirstName] = src.[FirstName]
            ,trg.[LastName] = src.[LastName]
            ,trg.[FullName] = src.[FullName]
            ,trg.[EmailAddress] = src.[EmailAddress]
            ,trg.[UpdatedAt] = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT 
        (
            [UserID]
            ,[FirstName]
            ,[LastName]
            ,[FullName]
            ,[EmailAddress]
        )
        VALUES 
        (
            src.UserID
            ,src.FirstName
            ,src.LastName
            ,src.FullName
            ,src.EmailAddress
        );
END

解答 #

1. 手动添加Null判断的有效性与效率 #

你提到的添加Null判断思路可行，但需要完整覆盖所有Null场景——只判断trg.EmailAddress IS NULL不够，还要考虑src.EmailAddress IS NULL的情况（比如目标字段非空但源字段为Null时，也需要触发更新）。

正确的字段差异判断逻辑是：两个值不相等，或者其中一个为Null而另一个不为Null。对应的表达式为：

(trg.EmailAddress <> src.EmailAddress 
 OR trg.EmailAddress IS NULL AND src.EmailAddress IS NOT NULL 
 OR trg.EmailAddress IS NOT NULL AND src.EmailAddress IS NULL)

或更简洁的等价写法：

NOT (trg.EmailAddress = src.EmailAddress OR (trg.EmailAddress IS NULL AND src.EmailAddress IS NULL))

这种手动判断方式是高效的，SQL Server查询优化器能很好处理这类条件，只要目标表的UserID（关联字段）及用于比较的字段有合适索引，性能不会有明显损耗。

2. 更简洁的替代方案：IS NOT DISTINCT FROM #

若使用SQL Server 2022及以上版本，可直接用标准SQL的IS NOT DISTINCT FROM运算符，它自动处理Null值比较（认为两个Null相等，Null和非Null不等）。字段差异判断可简化为：

NOT (trg.FirstName IS NOT DISTINCT FROM src.FirstName)

该写法更简洁易读，执行效率与手动Null判断基本一致。

3. 优化后的存储过程代码 #

兼容所有SQL Server版本的写法

CREATE PROCEDURE [dbo].[uspMergeUser]
(
    @User [dbo].[UserType] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    MERGE dbo.User AS trg
    USING @User AS src
        ON (trg.UserID = src.UserID )
    WHEN MATCHED AND 
        (
            -- 判断FirstName差异（含Null场景）
            trg.FirstName <> src.FirstName 
            OR trg.FirstName IS NULL AND src.FirstName IS NOT NULL 
            OR trg.FirstName IS NOT NULL AND src.FirstName IS NULL
            -- 判断LastName差异
            OR trg.LastName <> src.LastName 
            OR trg.LastName IS NULL AND src.LastName IS NOT NULL 
            OR trg.LastName IS NOT NULL AND src.LastName IS NULL
            -- 判断FullName差异
            OR trg.FullName <> src.FullName 
            OR trg.FullName IS NULL AND src.FullName IS NOT NULL 
            OR trg.FullName IS NOT NULL AND src.FullName IS NULL
            -- 判断EmailAddress差异
            OR trg.EmailAddress <> src.EmailAddress 
            OR trg.EmailAddress IS NULL AND src.EmailAddress IS NOT NULL 
            OR trg.EmailAddress IS NOT NULL AND src.EmailAddress IS NULL
        )
   THEN
        UPDATE SET 
            trg.[FirstName] = src.[FirstName]
            ,trg.[LastName] = src.[LastName]
            ,trg.[FullName] = src.[FullName]
            ,trg.[EmailAddress] = src.[EmailAddress]
            ,trg.[UpdatedAt] = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT 
        (
            [UserID]
            ,[FirstName]
            ,[LastName]
            ,[FullName]
            ,[EmailAddress]
        )
        VALUES 
        (
            src.UserID
            ,src.FirstName
            ,src.LastName
            ,src.FullName
            ,src.EmailAddress
        );
END

SQL Server 2022+简化写法

CREATE PROCEDURE [dbo].[uspMergeUser]
(
    @User [dbo].[UserType] READONLY
)
AS
BEGIN
    SET NOCOUNT ON;
    
    MERGE dbo.User AS trg
    USING @User AS src
        ON (trg.UserID = src.UserID )
    WHEN MATCHED AND 
        (
            NOT (trg.FirstName IS NOT DISTINCT FROM src.FirstName)
            OR NOT (trg.LastName IS NOT DISTINCT FROM src.LastName)
            OR NOT (trg.FullName IS NOT DISTINCT FROM src.FullName)
            OR NOT (trg.EmailAddress IS NOT DISTINCT FROM src.EmailAddress)
        )
   THEN
        UPDATE SET 
            trg.[FirstName] = src.[FirstName]
            ,trg.[LastName] = src.[LastName]
            ,trg.[FullName] = src.[FullName]
            ,trg.[EmailAddress] = src.[EmailAddress]
            ,trg.[UpdatedAt] = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN
        INSERT 
        (
            [UserID]
            ,[FirstName]
            ,[LastName]
            ,[FullName]
            ,[EmailAddress]
        )
        VALUES 
        (
            src.UserID
            ,src.FirstName
            ,src.LastName
            ,src.FullName
            ,src.EmailAddress
        );
END

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