SQL Server 2014 nvarchar(max) 结果的哈希字节为 nvarchar(max)

使用 SQL Server 2014 我有一个表nvarchar(max)称为的列[ASCII File]它可以包含许多 K 的 ASCII 文本文件。然后我想对该文件执行 MD5 哈希字节，并且生成的哈希值应始终为 20 字节。

好吧，当我选择hashbytes('MD5', [ASCII File])我完成查询但有错误

消息 8152，16 级，状态 10，第 4 行
字符串或二进制数据将被截断。

当我尝试时，我收到同样的消息

left(hashbytes('MD5', [ASCII File]), 50)

当我尝试时，我收到同样的消息

convert(varchar(50), hashbytes('MD5', [ASCII File]))

似乎因为我正在执行哈希字节的列是nvarchar(max)，hashbytes 函数的结果也是nvarchar(max).

你能告诉我如何才能使结果达到预期的 20 长，而不是太长以至于必须被截断吗？

似乎因为我正在执行哈希字节的字段是 nvarchar(max)，所以哈希字节的结果是 nvarchar(max)。

不，这是不可能的，特别是因为返回值哈希字节 https://learn.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql is a VARBINARY。另外，由于您的测试只是 SELECT 语句而不是 INSERT 语句，因此返回值无法获得截断错误。截断误差来自input价值。正如链接的 MSDN 页面中所述HASHBYTES（对于 SQL Server 2012 和 2014）：

允许的输入值限制为 8000 字节。输出符合算法标准：MD2、MD4、MD5为128位（16字节）； SHA 和 SHA1 为 160 位（20 字节）； SHA2_256 为 256 位（32 字节），SHA2_512 为 512 位（64 字节）。

这确实说明了一切：输入限制为 8000 字节，输出是基于指定算法的固定字节数。

SQL Server 2016 的更新文档（已删除 8000 字节限制）指出：

对于 SQL Server 2014 及更早版本，允许的输入值限制为 8000 字节。

您可以运行一个简单的测试：

DECLARE @Test NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000);
SELECT LEN(@Test);
SELECT HASHBYTES('MD5', @Test);

Returns:

50000

Msg 8152, Level 16, State 10, Line 3
String or binary data would be truncated.

如果要在 2016 年之前的 SQL Server 版本中向哈希函数传递超过 8000 个字节，则需要使用 SQLCLR。您可以编写自己的函数，也可以下载并安装免费版本SQL# https://SQLsharp.com/?ref=so_38670722SQLCLR 库（我创建的），并使用实用程序哈希值 and Util_HashBinary功能：

DECLARE @Test NVARCHAR(MAX) = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000);
SELECT LEN(@Test);
SELECT SQL#.Util_Hash('MD5', CONVERT(VARBINARY(MAX), @Test));
SELECT SQL#.Util_HashBinary('MD5', CONVERT(VARBINARY(MAX), @Test));

Returns:

50000
40752EB301B41EEAEB309348CE9711D6
0x40752EB301B41EEAEB309348CE9711D6

UPDATE

在使用的情况下VARCHAR(MAX)列或变量，但字符数不超过 8000 个（或NVARCHAR(MAX)包含 4000 个或更少字符的列或变量），不会有问题，一切都会按预期工作：

DECLARE @Test VARCHAR(MAX) = REPLICATE('t', 5000);
SELECT LEN(@Test) AS [Characters], 
       HASHBYTES('MD5', @Test) AS [MD5];

Returns:

5000    0x6ABFBA10B49157F2EF8C85862B6E6313