HASHBYTES: 8K byte input limit? - Jamie Collins

This...

DECLARE @input_text VARCHAR(MAX);
SET @input_text = <an 8000 byte string>;
SELECT DATALENGTH(HASHBYTES('SHA1', @input_text);

...returns 20.

Whereas this...

DECLARE @input_text VARCHAR(MAX);
SET @input_text = <an 8001 byte string>;
SELECT DATALENGTH(HASHBYTES('SHA1', @input_text);

fails with the message, "String or binary data would be truncated."

BOL tells me:

@input is varchar, nvarchar, or varbinary
Return Value: varbinary (maximum 8000 bytes)

Is the input restricted to 8000 bytes? If so, considering in 8000 byte
input returns a 20 byte value for SHA1 (other currently-available
hashing algorithms generate a shorter return value), how can I
generate anything close to an 8000 byte return value? is this merely
(extreme) future proofing?

Thanks,
Jamie.

--

HASHBYTES: 8K byte input limit? - Gert-Jan Strik

Jamie,

The second situation you mention does not return an error when I run it.
If the problem still persists, then please mention your exact SQL Server
version, and post a repro script (so including the 8001 byte string).

--
Gert-Jan
SQL Server MVP

HASHBYTES: 8K byte input limit? - Plamen Ratchev

Gert-Jan,

It seems to return the error as noted. Try this:

DECLARE @input_text VARCHAR(MAX);
SET @input_text = REPLICATE(CAST('a' AS VARCHAR(MAX)), 8001);
SELECT DATALENGTH(HASHBYTES('SHA1', @input_text));

BOL doesn't seem to mention the length for the input string:
http://msdn.microsoft.com/en-us/library/ms174415(SQL.100).aspx

Plamen Ratchev
http://www.SQLStudio.com

HASHBYTES: 8K byte input limit? - Gert-Jan Strik

Hmm, strange that I didn't get the error before. I get it now (with your
example).

But I agree with the OP. The documentation does not match the behavior.
BOL specifies that the *Return Value* is a varbinary of no more than
8000 bytes. And (although probably correct) that is a bit odd, since the
algorithm will define the size of the hash, and a hash value of 8000
bytes is hardly useful. But is does not describe any limits on the input
data.

If you ask me, the documentation should state that "'input'" and
change the example, and no longer use nvarchar(max) there...

--
Gert-Jan
SQL Server MVP

HASHBYTES: 8K byte input limit? - Michael Ware

Joseph,

excuse my ignorance, what does <BOL> stand for?

The msdn site does mention that 8000 is the limit on varchar's:

http://msdn.microsoft.com/en-us/library/ms176089(SQL.100).aspx

I suspect he is hitting a limit on the underlying data type rather than
anything specific on the function call, but don't know enough about what I'm
talking about to be sure.

-Mike


----------------------
The most serious charge which can be brought against New England is not
Puritanism but February.
-Joseph Wood Krutch

HASHBYTES: 8K byte input limit? - Plamen Ratchev

BOL stands for Books OnLine (or SQL Server Books Online), the official
documentation for SQL Server.


Plamen Ratchev
http://www.SQLStudio.com

HASHBYTES: 8K byte input limit? - Plamen Ratchev

This is what I was trying to say, the documentation example does not look
correct, or at least is missing the necessary explanation for the "input".

Plamen Ratchev
http://www.SQLStudio.com

HASHBYTES: 8K byte input limit? - Michael Ware

Plamen,

Thank you for that,  so many acronyms, so little time...

-Mike
----------------------
Be careful about reading health books.
You may die of a misprint.
~Mark Twain

HASHBYTES: 8K byte input limit? - Gert-Jan Strik

Michael,

The datatype is just fine. The URL you posted mentions that if you want
to specify an exact (maximum) size, then you can only specify a value
between 1 and 8000. If you use "max", then the maximum storage size is
characters.

It just seems that the function HASHBYTES cannot handle a varchar over
8000 characters, because that is where the error occurs.

FYI: BOL stands for Books Online, the electronic documentation that
comes with SQL Server. Plamen posted the URL of the same page on
Microsoft's site.

--
Gert-Jan
SQL Server MVP

HASHBYTES: 8K byte input limit? - Erland Sommarskog

Plamen Ratchev (Plamen@SQLStudio.com) writes:

So did anyone of you submit BOL Feedback on the topic?


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

HASHBYTES: 8K byte input limit? - Gert-Jan Strik

Nope (at least not me). I choose to restrict myself to dialog, such as
in this newsgroup...

--
Gert-Jan

HASHBYTES: 8K byte input limit? - Plamen Ratchev

Erland,

I submitted feedback with example shortly after my post, had no time to post
here.

Plamen Ratchev
http://www.SQLStudio.com

HASHBYTES: 8K byte input limit? - Plamen Ratchev

BTW, there is apparently already item on Connect:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=273429

Plamen Ratchev
http://www.SQLStudio.com

HASHBYTES: 8K byte input limit? - Jamie Collins

Thanks everyone. I've now visited the link and 'voted' for the fix :)

Jamie.

--