如何让INTERSECT运算符在时态表更新中区分大小写？

问题背景 #

我数据库中有多个系统版本化（时态）表，根据微软文档，对时态表执行任何数据修改查询时，即使列值未变化，数据库引擎也会向历史表添加一行。为避免历史表产生冗余行，我在执行UPDATE命令前用INTERSECT运算符确认数据是否实际变更，语句如下：

UPDATE [TemporalTable]
    SET [Col1] = b.[Col1]...
       ,[Coln] = b.[Coln]
    FROM [TemporalTable] a
    JOIN [UpdateTable] b
    ON a.[Id] = b.[Id]
    WHERE NOT EXISTS (SELECT a.[Col1]...
                            ,a.[Coln]
                      INTERSECT
                      SELECT b.[Col1]...
                            ,b.[Coln])

但INTERSECT不区分大小写，比如列数据从“hello there”改为“Hello there”时，时态表不会更新，我希望能反映这类大小写变化。纠结于修改数据库/表/列的排序规则，还是临时用COLLATE子句，既不想永久修改产生意外，又担心临时COLLATE影响性能，求建议。

解决方案建议 #

1. 临时使用COLLATE子句（优先推荐） #

如果不想改动现有数据库或列的排序规则，最安全的方式是在INTERSECT的查询中对需要区分大小写的列指定区分大小写的排序规则（比如Latin1_General_CS_AS）。这样只会在当前查询生效，不会影响其他业务。

修改后的示例语句：

UPDATE [TemporalTable]
    SET [Col1] = b.[Col1]...
       ,[Coln] = b.[Coln]
    FROM [TemporalTable] a
    JOIN [UpdateTable] b
    ON a.[Id] = b.[Id]
    WHERE NOT EXISTS (
        SELECT 
            a.[Col1] COLLATE Latin1_General_CS_AS,
            a.[Col2] COLLATE Latin1_General_CS_AS,
            ...,
            a.[Coln] COLLATE Latin1_General_CS_AS
        INTERSECT
        SELECT 
            b.[Col1] COLLATE Latin1_General_CS_AS,
            b.[Col2] COLLATE Latin1_General_CS_AS,
            ...,
            b.[Coln] COLLATE Latin1_General_CS_AS
    )

关于性能顾虑：

• 仅对需要区分大小写的列添加COLLATE，而非所有列，能减少性能开销。
• 若UPDATE操作的关联条件a.Id = b.Id已有索引，INTERSECT部分的性能影响通常在可接受范围内，尤其是当需要更新的行数占比不高时。
• 可通过查看执行计划、测试小批量数据，确认性能是否符合预期。

2. 修改特定列的排序规则（谨慎使用） #

如果业务上这些列本身就应该区分大小写（比如用户名、特定标识），可以考虑修改列的排序规则为区分大小写的类型。但要注意：

• 修改列排序规则会锁表，且需要更新现有数据的排序规则，建议在维护窗口操作。
• 必须确认所有依赖该列的查询、存储过程、应用代码都能兼容区分大小写的比较，避免出现逻辑错误（比如之前的相等查询因大小写不匹配返回空）。

3. 修改数据库/表的默认排序规则（不推荐） #

修改整个数据库或表的默认排序规则影响范围极大，会导致所有未指定排序规则的列都继承该规则，很可能引发大量业务逻辑错误（比如登录验证、数据匹配等场景突然区分大小写），除非确定整个系统都需要区分大小写，否则不建议这么做。

总结 #

优先选择临时使用COLLATE子句的方案，既能满足区分大小写的更新需求，又不会对现有业务产生意外影响。如果性能确实有问题，再评估是否修改特定列的排序规则，修改前一定要做好充分的测试和备份。

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