SQL Server 2014 Hashbytes of nvarchar (max) result nvarchar (max)

Question

Using SQL Server 2014, I have a table with a column nvarchar(max)with a name [ASCII File]that can contain an ASCII text file for many K. Then I want to make MD5 hash files in this file, and the resulting hash should always be 20 bytes.

Well, when I make a choice hashbytes('MD5', [ASCII File]), I get a request with errors

Msg 8152, Level 16, State 10, Line 4
String or binary data will be truncated.

I get the same message when I try

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

I get the same message when I try

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

It seems that since the column I'm doing is hashbytes on is nvarchar(max), the result of the hashbytes function is also nvarchar(max).

, , 20, ?

Answer 1

, , , nvarchar (max), - - nvarchar (max).

, , HASHBYTES VARBINARY. , SELECT, INSERT, . . MSDN HASHBYTES ( SQL Server 2012 2014):

8000 . : 128 (16 ) MD2, MD4 MD5; 160 (20 ) SHA SHA1; 256 (32 ) SHA2_256 512 (64 ) SHA2_512.

: 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);

:

50000

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

8000 - SQL Server 2016 , SQLCLR. , SQL # SQLCLR ( ), Util_Hash 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));

:

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];

:

5000    0x6ABFBA10B49157F2EF8C85862B6E6313

Answer 2

SQL Server 2016 HASHBYTES.

DECLARE @Test NVARCHAR(MAX); 
SET @Test = REPLICATE(CONVERT(NVARCHAR(MAX), N't'), 50000000);
SELECT LEN(@Test);
SELECT HASHBYTES('SHA2_512', @Test);

HASHBYTES (Transact-SQL)

Answer 3

Input length limit of 8,000 bytes for the HASHBYTES (Transact-SQL) function is removed in sql 2016

According to the algorithm below, the output data size is 128 bits (16 bytes) for MD2, MD4 and MD5; 160 bits (20 bytes) for SHA and SHA1; 256 bits (32 bytes) for SHA2_256 512 bits (64 bytes) for SHA2_512.