如何将多列合并为唯一值供HASHBYTES使用？现有方案能否优化？

1. 能否构造不同行导致脚本失效？ #

这取决于你的两个脚本是否做了无歧义的序列化处理：

• 对于脚本1（NVARCHAR合并）：
  如果仅用普通分隔符（如|、,）且未处理列值包含分隔符的情况，很容易构造出冲突行。比如：
  • 行1：列A=N'x|y'，列B=N'z'
  • 行2：列A=N'x'，列B=N'|yz'
    若用|做分隔符，合并后的字符串都是x|y|z，最终HASH值完全相同，但两行实际数据不同。
    另外，如果把NULL直接替换为空字符串，会导致NULL和N''（空字符串）的差异被掩盖，也会产生相同的HASH值。
• 对于脚本2（VARBINARY合并）：
  如果未对NULL做特殊标记（比如直接用CAST(col AS VARBINARY)，NULL会被转为NULL，拼接时会导致整个VARBINARY结果为NULL），会让所有含NULL的行HASH值相同。此外，如果用某个固定VARBINARY值（如0xFFFF）标记NULL，而恰好某列的VARBINARY值就是0xFFFF，也会导致不同行生成相同的HASH。
• 关于SHA2_256哈希碰撞：理论上存在，但目前没有公开的可构造方法，实际业务场景中可以认为不会发生。所以脚本失效的核心原因几乎都是序列化阶段的歧义，而非哈希本身。

2. 更好更快的生成方法与通用最佳实践 #

要满足你的四条规则，核心是实现无歧义的行数据序列化，再结合加密哈希（如SHA2_256）生成唯一标识。以下是具体方案和最佳实践：

通用原则 #

• 必须保留所有差异：包括NULL与空字符串、列顺序、尾随空格、不可见字符、数据类型差异（如INT 123和NVARCHAR '123'）。
• 序列化必须无歧义：避免分隔符冲突、类型混淆问题。

推荐实现方案 #

方案一：带类型与NULL标记的VARBINARY序列化（性能最优）

利用VARBINARY直接拼接，避免字符串处理的开销，同时给每个列添加类型和NULL标记：

SELECT
  HASHBYTES('SHA2_256',
    -- INT列：标记+值（NULL用特殊标记）
    CASE WHEN IntCol IS NULL THEN 0x01000000 ELSE 0x02 + CAST(IntCol AS VARBINARY(4)) END
    -- NVARCHAR列1：标记+值（NULL用特殊标记）
    + CASE WHEN NvarcharCol1 IS NULL THEN 0x03 ELSE 0x04 + CAST(NvarcharCol1 AS VARBINARY(200)) END
    -- NVARCHAR列2
    + CASE WHEN NvarcharCol2 IS NULL THEN 0x05 ELSE 0x06 + CAST(NvarcharCol2 AS VARBINARY(200)) END
    -- NVARCHAR列3
    + CASE WHEN NvarcharCol3 IS NULL THEN 0x07 ELSE 0x08 + CAST(NvarcharCol3 AS VARBINARY(200)) END
  ) AS COMBINED_VALUE
FROM YourTable;

• 每个列用不同的单字节标记区分「NULL」和「非NULL」，同时区分INT和NVARCHAR类型，彻底避免歧义。
• VARBINARY拼接速度远快于NVARCHAR字符串拼接，适合大数据量场景。

方案二：无歧义字符串序列化（兼容性好）

如果需要可读性（不推荐，因为HASH后不可读），可以用长度前缀+特殊NULL标记的方式：

SELECT
  HASHBYTES('SHA2_256',
    CONCAT(
      -- INT列：类型标记+长度+值/NULL标记
      N'INT:', CASE WHEN IntCol IS NULL THEN N'NULL' ELSE CONCAT(LEN(CAST(IntCol AS NVARCHAR(11))), N':', CAST(IntCol AS NVARCHAR(11))) END, N'|',
      -- NVARCHAR列1：类型标记+长度+值/NULL标记
      N'NVARCHAR:', CASE WHEN NvarcharCol1 IS NULL THEN N'NULL' ELSE CONCAT(LEN(NvarcharCol1), N':', NvarcharCol1) END, N'|',
      -- NVARCHAR列2
      N'NVARCHAR:', CASE WHEN NvarcharCol2 IS NULL THEN N'NULL' ELSE CONCAT(LEN(NvarcharCol2), N':', NvarcharCol2) END, N'|',
      -- NVARCHAR列3
      N'NVARCHAR:', CASE WHEN NvarcharCol3 IS NULL THEN N'NULL' ELSE CONCAT(LEN(NvarcharCol3), N':', NvarcharCol3) END
    )
  ) AS COMBINED_VALUE
FROM YourTable;

• 用类型:长度:值的格式，彻底避免分隔符冲突（因为长度前缀明确了每个列值的边界）。
• 明确标记NULL，区分NULL和空字符串。

最佳实践总结 #

1. 优先选择VARBINARY序列化：性能更高，避免字符串转义、分隔符冲突等问题。
2. 必须显式处理NULL：用独特标记区分NULL与空值，不能直接替换为空字符串。
3. 区分数据类型：比如INT和NVARCHAR的相同数值（如123和'123'）必须生成不同的序列化结果。
4. 使用加密哈希算法：优先选SHA2_256或SHA512，避免使用BINARY_CHECKSUM、CHECKSUM这类非加密哈希（碰撞概率高）。
5. 避免依赖分隔符的简单拼接：除非能确保列值永远不包含分隔符，否则必须用长度前缀或类型标记来消除歧义。

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