HASHBYTES limits

Question

HASHBYTES limits

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
December 20, 2016 at 10:22 pm

#324137

Comments posted to this topic are about the item HASHBYTES limits
December 20, 2016 at 10:24 pm

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Umer Akram SSC-Insane Points: 22209 More actions · Answer 1

Stewart "Arturius" Campbell (12/20/2016)
That is a really cool improvement (i wasn't aware it had changed, so learned something)
nice question, thanks Steve

+1

edwardwill SSCertifiable Points: 5789 More actions · Answer 2

The article you cite in support of the answer is silent on the limits to the size of the input string.

James Lean SSCertifiable Points: 5757 More actions · Answer 3

I think the documentation is slightly misleading on this. I've tried the below on SQL 2014 and 2016, and they both give the same result, namely the same hash value for the string of 8000 and 8001 bytes:

PRINT HASHBYTES('SHA2_512', REPLICATE('a', 8000));

PRINT HASHBYTES('SHA2_512', REPLICATE('a', 8001));

0x392166E678BD0B7CDDADCE283F7EF83440519B803A8DAF03F542F685A81A1E1550A5E0CDD87C626F543C68FE3B354F59BD285921C40F11CF1D04BC880867F23D

So it seems as though HASHBYTES will silently truncate any value passed in to 8000 bytes.

-----
JL

George Vobr SSCrazy Eights Points: 9914 More actions · Answer 4

edwardwill (12/21/2016)
The article you cite in support of the answer is silent on the limits to the size of the input string.

Yes in the MSDN HASHBYTES (Transact-SQL) there is no clearly given limit, and there is a warning

when using e.g. varchar(max). I thought so, that question is targeted only at the possible max. limit

and used the info from MSDN What's New in SQL Server 2016 (Database Engine) https://msdn.microsoft.com/en-us/library/bb510411.aspx

I'm sorry. :ermm: The warning "Each non-null varchar(max) or nvarchar(max) column requires 24 bytes of additional fixed allocation which counts against the 8,060 byte row limit during a sort operation. This can create an implicit limit to the number of non-null varchar(max) or nvarchar(max) columns that can be created in a table..." is stated in the MSDN Data Types (Transact-SQL) for varchar and nvarchar https://msdn.microsoft.com/en-us/library/ms176089.aspx

sknox SSChampion Points: 12388 More actions · Answer 5

James Lean (12/21/2016)
I think the documentation is slightly misleading on this. I've tried the below on SQL 2014 and 2016, and they both give the same result, namely the same hash value for the string of 8000 and 8001 bytes:
PRINT HASHBYTES('SHA2_512', REPLICATE('a', 8000));
PRINT HASHBYTES('SHA2_512', REPLICATE('a', 8001));
0x392166E678BD0B7CDDADCE283F7EF83440519B803A8DAF03F542F685A81A1E1550A5E0CDD87C626F543C68FE3B354F59BD285921C40F11CF1D04BC880867F23D
0x392166E678BD0B7CDDADCE283F7EF83440519B803A8DAF03F542F685A81A1E1550A5E0CDD87C626F543C68FE3B354F59BD285921C40F11CF1D04BC880867F23D
So it seems as though HASHBYTES will silently truncate any value passed in to 8000 bytes.

Actually, what's happening in your test is that REPLICATE() is silently truncating its result. From https://msdn.microsoft.com/en-us/library/ms174383.aspx:

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

Neil Burton SSC-Insane Points: 22905 More actions · Answer 6

edwardwill (12/21/2016)
The article you cite in support of the answer is silent on the limits to the size of the input string.

Technically it's not.

For SQL Server 2014 and earlier, allowed input values are limited to 8000 bytes.

I inferred from this that in 2016 the input value was 'unlimited' so it would accept the limit of VARCHAR(MAX).

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

edwardwill SSCertifiable Points: 5789 More actions · Answer 7

BWFC (12/21/2016)
edwardwill (12/21/2016)
The article you cite in support of the answer is silent on the limits to the size of the input string.
Technically it's not.

Then please point me to the point in the article, not the comments under the article, where the limit on the size of the input to this function is specified.

James Lean SSCertifiable Points: 5757 More actions · Answer 8

sknox (12/21/2016)
Actually, what's happening in your test is that REPLICATE() is silently truncating its result

Ah yes, good spot :hehe:

So the below works fine on 2016, and gives two different hash values, but on 2014 the second statement gives a "String or binary data would be truncated" error. Which seems to confirm the suggestion in the doc.

PRINT HASHBYTES('SHA2_512', REPLICATE(CAST('a' AS varchar(max)), 8000));

PRINT HASHBYTES('SHA2_512', REPLICATE(CAST('a' AS varchar(max)), 8001));

-----
JL

Neil Burton SSC-Insane Points: 22905 More actions · Answer 9

edwardwill (12/21/2016)
BWFC (12/21/2016)
edwardwill (12/21/2016)
The article you cite in support of the answer is silent on the limits to the size of the input string.
Technically it's not.
Then please point me to the point in the article, not the comments under the article, where the limit on the size of the input to this function is specified.

The limit is not specified in the article. The line I quoted is in the Arguments section of the article. From this, as I said, I inferred that the limit was that of VARCHAR(MAX). Granted, it is not explicit but there was a reference to input limits on which I based my answer. It seemed logical to me that if there was no reference to an input limit in SQL Server 2016, there was no input limit other than that of VARCHAR(MAX).

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

Sean Lange SSC Guru Points: 286573 More actions · Answer 10

The documentation on this is appallingly awful. Nowhere does it actually state what the input limit is, it only mentions that it used to be limited to 8,000 characters. To make things even more confusing somebody posted a comment just this month that it seems to be limited to 8,000. Yet another shining example of how poor the documentation is on this software.

Steve's question: 1

MSDN Documentation: 0

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Revenant SSC-Forever Points: 42467 More actions · Answer 11

Revenant

SSC-Forever

Points: 42467

December 21, 2016 at 9:05 am

#1919178

I am glad I was not the only one... Thanks, Steve!

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 12

Nice question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP