Binary_Checksum与HashBytes函数

Question

我有一个复杂的查询，它使用了很多二进制校验和函数，当我用两个不同记录的测试数据测试它时，它实际上返回了相同的校验和值。请查找我在下面使用的测试数据

SELECT BINARY_CHECKSUM(16   ,'EP30461105',1) AS BinaryCheckSumEx UNION ALL
SELECT BINARY_CHECKSUM(21   ,'EP30461155',1) AS BinaryCheckSumEx

现在，我尝试使用带有“MD5”算法的HASHBYTES函数，对于该算法，我肯定会得到唯一的记录，但现在我关心的是，在当前的查询中，我使用‘校验和’值来加入我的“合并”语句，以查找新的记录。因为'HashBytes‘返回了Varbinary类型，所以当我用'HashByte’字段替换连接条件时，性能开销会有多大。

SELECT HASHBYTES('MD5', CONCAT(Col1,Col2,Col3,Col4,..))

而且，我需要为多个列创建散列，在这种情况下，我需要一个额外的Concat函数，这会给我的性能带来额外的开销。

Answer 1

以下是几种选择：

1. 使用哈希索引作为VARBINARY

- It’s not a good idea. check these links:
- [https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35b61bb0-1fa8-4a2f-a9fb-729a1874dcf8/clustered-index-on-a-varbinary-column?forum=sqldatabaseengine](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/35b61bb0-1fa8-4a2f-a9fb-729a1874dcf8/clustered-index-on-a-varbinary-column?forum=sqldatabaseengine)
- [SQL Server indexing - varchar(100) vs varbinary(100)? [convert data]](https://stackoverflow.com/questions/30540749/sql-server-indexing-varchar100-vs-varbinary100-convert-data)

​

1. 使用BINARY_CHECKSUM和校验和

- It’s good but the problem is there is a high chance of duplication in checksum and as you google you see that many people have a problem with it.

​

但是，校验和不会改变的可能性很小。因此，我们不建议使用校验和来检测值是否已经更改，除非应用程序可以容忍偶尔缺少更改。考虑使用HashBytes代替。当指定MD5哈希算法时，HashBytes对两个不同输入返回相同结果的概率大大低于校验和的概率。

来源：https://msdn.microsoft.com/en-us/library/ms189788(v=SQL.100).aspx

1. 将HASBYTES转换为BIGINT并在该上建立索引
   • 这不是个好主意

​

我在将哈希值转换为BIGINT时也要小心，因为BIGINT只有8个字节，但是所有哈希算法--甚至是MD5 --都大于8个字节(MD5 = 16字节，SHA1 = 20，SHA2_256 = 32，SHA2_512 = 64)。并将大于8个字节的二进制值转换为BIGINTsilently截断值。因此，你失去了准确性，出现了越来越多的假阳性。以下查询显示此行为：

SELECT CONVERT(BIGINT, 0xFFFFFFFFFFFFFF),      --  7 bytes = 72057594037927935
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFF),    --  8 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFF),  --  9 bytes = -1
       CONVERT(BIGINT, 0xFFFFFFFFFFFFFFFFFFFF) -- 10 bytes = -1

来源：https://dba.stackexchange.com/questions/154945/index-maintenance-for-varbinary

1. 将HASHBYTES转换为VARCHAR并对该进行索引
   • 这是个不错的选择
   • 你有两个选择：

​

( a)如果使用SQL 2008或更高版本

SELECT CONVERT(NVARCHAR(32),HashBytes('MD5', CONTENT),2)

( b)如果使用SQL 2005

SELECT SUBSTRING(master.dbo.fn_varbintohexstr(HashBytes('MD5', CONTENT)), 3, 32)

PS:如果您想知道您应该使用哪种哈希算法：

MD5 = 16 bytes
SHA1 = 20 bytes
SHA2_256 = 32 bytes
SHA2_512 = 64 bytes

​

​

来源：https://blogs.msdn.microsoft.com/sqlsecurity/2011/08/26/data-hashing-in-sql-server/

对于第二个问题，您应该使Hash列持久化，以避免对运行每个查询的影响。