SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 8000)) returns a 20-byte value, and SELECT HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)) returns the same warning message and no value.
Why the IF comparison between the two doesn't abort but chooses to execute the ELSE clause is, in all honesty, beyond me. I firsth thought it was because the too-long input causes the function to return NULL (in addition to the message), but when I use IF HASHBYTES(...) IS NULL, I still got the "no" result.
Anyhoo, I stand corrected. The HASHBYTES does apparently indeed have an (undocumented!!) limit of 8000 bytes on it's input string. Thanks for pointing that out!
Despite the fact that I knew that Hashbytes has an 8000 byte limit on its input, I was stumped at the very point you have highlighted above - I thought it would error out. But it doesn't.
The interesting thing I found when I did some testing on my sql server 2008 developer edition is that:
When I run the code -
select HASHBYTES('sha1', REPLICATE(cast('a' as varchar(max)), 9000))
I get the error msg as indicated by you.
BUT when I run
select HASHBYTES('sha1', REPLICATE('a', 9000))
I do not get an error but a certain Hashbyte output. I realized further that actually, Replicate('a', 9000) was actually Replicating the 'A' value upto 8000 bytes and ignoring the rest. It wasn't erroring out. So in effect, the input condition to HASHBYTES in the question posted wasn't getting violated as presented in the QoTD.
"
However, casting the 'A' to Varchar(Max) and then replicating it 9000 times produces 9000 "A's" and THIS causes the input to HASHBYTES to exceed the permissible limit.
Running this code produces an ERROR Message BUT and the Output NO.
IF HASHBYTES('SHA1', REPLICATE(cast('A' as varchar(max)), 9000)) = HASHBYTES('SHA1', REPLICATE('A', 8000))
PRINT 'yes.'
ELSE PRINT 'no.'
Saurabh Dwivedy
___________________________________________________________
My Blog: http://tinyurl.com/dwivedys
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537