Oracle STANDARD_HASH函数及虚拟列在SQL Server中的等效实现方法

原Oracle逻辑拆解 #

待迁移的虚拟列逻辑如下：

• 拼接字段：将SSN、分隔符|、DATE_OF_BIRTH按DD-MON-RR格式转换后的字符串拼接为整体
• 哈希计算：对拼接结果执行SHA256哈希，输出长度为32的RAW类型虚拟列

SQL Server对应实现方案 #

SQL Server中的计算列完全等效于Oracle的虚拟列，搭配HASHBYTES函数即可实现相同逻辑，建列代码如下：

-- 非持久化计算列（和Oracle VIRTUAL属性一致，查询时实时计算，不占用存储空间）
MY_HASH AS CAST(HASHBYTES('SHA2_256', CONCAT(SSN, '|', FORMAT(DATE_OF_BIRTH, 'dd-MMM-yy', 'en-US'))) AS VARBINARY(32)),

-- 如需持久化存储/对该列建索引，添加PERSISTED关键字即可
MY_HASH AS CAST(HASHBYTES('SHA2_256', CONCAT(SSN, '|', FORMAT(DATE_OF_BIRTH, 'dd-MMM-yy', 'en-US'))) AS VARBINARY(32)) PERSISTED,

对齐哈希结果的注意事项 #

• 日期格式对齐：FORMAT函数第三个参数指定en-US，保证月份输出为英文缩写（如JAN、FEB），和Oracle英文环境下TO_CHAR的DD-MON-RR输出完全一致；如果Oracle使用非英文语言环境，对应修改第三个参数即可。
• RR规则适配：FORMAT的yy格式符默认遵循和Oracle RR相同的世纪判定规则：年份00-49归属21世纪，50-99归属20世纪。
• 编码对齐：如果出现两边哈希结果不一致的情况，检查Oracle数据库的字符集，将SQL Server拼接后的字符串转成对应编码即可，示例（Oracle字符集为GBK时）：

  MY_HASH AS CAST(HASHBYTES('SHA2_256', CONVERT(VARCHAR(MAX), CONCAT(SSN, '|', FORMAT(DATE_OF_BIRTH, 'dd-MMM-yy', 'en-US')), 936)) AS VARBINARY(32)),
  

• 版本兼容：如果使用SQL Server 2012以下版本，不支持CONCAT函数，替换为+号拼接即可，注意处理NULL值：

  MY_HASH AS CAST(HASHBYTES('SHA2_256', ISNULL(SSN, '') + '|' + ISNULL(FORMAT(DATE_OF_BIRTH, 'dd-MMM-yy', 'en-US'), '')) AS VARBINARY(32)),
  

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