Sql -批量加密哈希生成

Question

我们正在尝试在out ETL进程中实现更改检测。

因此，我们决定使用以下命令获取加密散列

SET a.[HASH] = (SELECT 
                    master.dbo.fn_varbintohexsubstring(0, HashBytes('md5', (SELECT TOP 1 * FROM customer_demographics_staging b WHERE b.customer_no = a.customer_no FOR XML RAW)), 1, 0))
FROM customer_demographics_staging a

对于一个有700k条记录和大约140列的表(我们还没有确定变化的列)，查询运行了大约半个小时，然后我们取消了它。

除了减少查询的数量之外，我们还可以改进这一点吗？

Answer 1

有几件事。如果HASH列的数据类型是varbinary(20)，则无需考虑将MD5散列转换为字符串；只需存储散列字节即可。为此，如果您希望使用加密散列进行更改检测，我将使用内联表值函数来实现。下面是我用AdventureWorks拼凑起来的一个例子：

ALTER TABLE [HumanResources].[Employee] ADD [Hash] VARBINARY(20) NULL;
GO
CREATE FUNCTION dbo.CalculateHash(@EmployeeID AS INT)
RETURNS TABLE
AS
    RETURN

    SELECT e.[BusinessEntityID], HASHBYTES('md5', (
        SELECT *
        FROM [HumanResources].[Employee] AS [e2]
        WHERE [e2].[BusinessEntityID] = e.[BusinessEntityID]
        FOR XML RAW
    )) AS [Hash]
    FROM [HumanResources].[Employee] AS [e]
    WHERE [e].[BusinessEntityID] = @EmployeeID

go
SELECT TOP 10 [e].*, ch.[Hash]
FROM [HumanResources].[Employee] AS [e]
CROSS APPLY dbo.[CalculateHash]([e].[BusinessEntityID]) AS [ch]
GO

也就是说，如果是我，我根本不会费心使用MD5，只会使用CHECKSUM()函数(可能作为表中的持久化计算列)。它支持本机获取多个列(这样就不会产生将行序列化为XML的开销)。

Answer 2

与Ben Thul已经说过的一样，我也倾向于依赖BINARY_CHECKSUM()，因为它易于使用。我同意这个函数返回“但是一个整数”，它是8个字节，而例如MD5将返回一个变量二进制(16)，它是字节数的两倍，所以你得到的是正方形(而不是双精度！)“结果空间”的意思是你最终发生碰撞的几率会小得令人难以置信。但是paranoid me想补充说，即使如此，MD5值的精确匹配并不意味着您也有相同的(输入)值！

老实说，我使用这个函数只是为了消除差异。如果校验和(或散列)的结果不同，那么您可以100%确定值也是不同的。如果它们是相同的，那么您仍然应该检查整个源值，看看是否没有“假匹配”。

您的用例似乎正好相反:您希望通过删除相同的用例来找到不同的用例，并通过只查看散列代码来缩短后者的用例。老实说，我不是这种方法的粉丝，因为你可能会遇到冲突，导致临时表中的“已更改”记录获得与旧记录完全相同的哈希值，因此当您想要复制更改时会被忽略。再说一次，这种可能性非常小，但就像我说的，当涉及到这一点时，我是偏执的=)

如果你想继续沿着这条路走下去，请注意以下几点：

• HashBytes仅支持8000字节的输入。考虑到XML语法增加的开销，您可能会在使用这140列
• 时遇到麻烦。我看不出有任何(很好的)理由在将HashBytes的结果写到表
• 之前将其转换为其他值。虽然FOR XML非常快，但CONCAT不是也同样快吗?同时还会产生一个“较小”的结果(参见第1点)？我同意它带来了自己的一组问题，比如当field1，field2，field3是“hello”时，"world“会导致"hello"，""，"world”=/你也可以通过CONCAT-ing每个字段的LEN()来解决这个问题……不确定我们还能获得多少收益=)
• 我猜您已经有了它，但是临时表中的customer_no字段上是否有一个索引，最好是唯一的和聚集的？