sql - 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

   • 这不是一个好主意。检查这些链接:
   • 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]

2. 使用 BINARY_CHECKSUM 和 CHECKSUM

   • 这很好，但问题是校验和很可能出现重复，当你用 Google 搜索时，你会发现很多人都遇到了问题。

However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

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

将 HASBYTES 转换为 BIGINT 并在其上建立索引

• 这不是一个好主意

I would also be careful about converting the hashed value to BIGINT given that BIGINT is only 8 bytes yet all hash algorithms -- even MD5 -- are greater than 8 bytes (MD5 = 16 bytes, SHA1 = 20, SHA2_256 = 32, and SHA2_512 = 64). And converting binary values larger than 8 bytes to BIGINTsilently truncates the values. Hence you lose accuracy and increasing occurrences of false positives. The following query shows this behavior:

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

将 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/

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