为何CONCAT函数中加入varchar(max)会导致查询计划性能骤降？

涉及的表记录量在100万到2亿条之间，作为原始数据源的暂存/落地表，这些表均无索引。加入CAST(NULL AS VARCHAR(MAX))是为了处理HASHBYTES函数拼接总长度超过8000字节的varchar(255)列的场景，开发者将该逻辑应用在了全加载流程中。该HASHBYTES函数用于增量加载时识别记录变更，但用小型临时表模拟该场景并未成功。

CAST (
        HASHBYTES
        (
            'SHA2_256',
            CONCAT
                (
                    [CTE].[TransactionDetailTypeCode], --varchar(50)
                    '|',
                    [CTE].[TransactionDetailAmount], --decimal(19,4)
                    '|',
                    [CTE].[OriginalTransactionPostDate], --date
                    '|',
                    [CTE].[SourceSystemCode], --varchar(50)
                    '|',
                    [CTE].[EtlSourceTransaction], --varchar(500)
                    '|',
                    [CTE].[EtlSourceTransactionDetail], --varchar(500)
                    '|',
                    CAST(NULL AS VARCHAR(MAX)) --COMMENT THIS LINE OUT
                )
        )
        AS varbinary(32)
    ) AS [EtlRowHashValue]

• 包含CAST(NULL AS VARCHAR(MAX))的查询计划：子树成本大幅提升，且存在强制排序用于合并连接的操作
• 注释掉该语句后的查询计划：子树成本仅为前者的1/22左右，执行计划更高效

测试发现加入CAST(NULL AS VARCHAR(MAX))后，子树成本是注释掉该语句后的22倍以上，且执行计划中出现了强制排序用于合并连接的操作，这应该是性能极差的核心原因。请解释是HASHBYTES、CONCAT还是其他因素导致加入该语句后查询计划发生如此剧烈的变化？是否由某些SQL Server底层机制引发？

核心原因：CONCAT返回类型变更触发底层执行逻辑连锁变化 #

当你在CONCAT中加入CAST(NULL AS VARCHAR(MAX))时，CONCAT函数的返回类型会从常规VARCHAR（最大8000字节）变为VARCHAR(MAX)，这一类型变化会触发多个底层机制的改变，最终导致执行计划和性能的剧烈波动：

1. HASHBYTES的大字段处理逻辑差异
   HASHBYTES对常规VARCHAR和VARCHAR(MAX)的处理路径完全不同：

• 对于常规VARCHAR（≤8000字节），HASHBYTES可直接在内存中完成哈希计算，执行效率极高；
• 对于VARCHAR(MAX)，SQL Server会将数据写入tempdb的工作文件进行处理，这会带来大量IO开销，同时触发额外的内存分配与数据序列化操作，直接推高子树成本。

2. 排序与合并连接的强制触发
   当CONCAT返回VARCHAR(MAX)时，SQL Server查询优化器会判定该字段的“行大小”显著增大（即使实际值是NULL），在后续连接操作中：

• 优化器会默认避免使用嵌套循环连接（大字段会导致单次循环的内存开销过高）；
• 转而选择合并连接，但合并连接要求输入数据有序，因此会强制添加排序操作——这就是你看到的“强制排序用于合并连接”的原因。排序操作在百万到亿级数据量下的开销极其巨大，直接导致子树成本飙升22倍以上。

3. 统计信息与基数估计偏差
   VARCHAR(MAX)的统计信息收集逻辑与常规VARCHAR不同，优化器无法准确估算该字段的实际数据分布（尤其是当值为NULL时），这会进一步导致基数估计偏差，让优化器做出更保守（也更低效）的执行计划选择。

替代方案建议 #

既然加入该语句是为了处理拼接长度超8000字节的场景，可采用更高效的方式：

• 仅在实际拼接长度可能超过8000字节的列上使用VARCHAR(MAX)转换，而非全局添加一个NULL的VARCHAR(MAX)；
• 若SQL Server版本支持，可使用STRING_AGG替代CONCAT，并结合CAST按需转换为VARCHAR(MAX)；
• 对于增量加载的哈希计算，可考虑拆分哈希字段，对多个小字段分别计算哈希后再合并，避免大字段拼接。

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