SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Comparing a hash


Comparing a hash

Author
Message
alastors
alastors
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 40
Comments posted to this topic are about the item Comparing a hash
ziangij
ziangij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3267 Visits: 377
thanks for the question... i tried 8000 byte limitation with LEN function and even that throws an error.


Argument data type text is invalid for argument 1 of len function.




i guess all sql functions either throw an error (e.g. LEN or HASHBYTES) or generally ignore any data more than 8000 bytes (e.g. REPLICATE)...
SanjayAttray
SanjayAttray
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4715 Visits: 1619
Huh.....never used it. Ever. something new to think.

Thanks for the question.

SQL DBA.
ChiragNS
ChiragNS
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4095 Visits: 1865
Knew the right answer. Clicked on the wrong option by mistake. ;-)

"Keep Trying"
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12151
Nice question. But the explanation is incorrect.

There is no 8000 byte limit on REPLICATE. But there is one on the VARCHAR data type, and since 'A' is considered to be VARCHAR, so will be the result of REPLICATE. Run the following code to see for yourself:

1. For lengths up to 8000, differences are detected as expected:
IF HASHBYTES('SHA1', REPLICATE('A', 4000)) = HASHBYTES('SHA1', REPLICATE('A', 8000))
PRINT 'yes.';
ELSE
PRINT 'no.';



2. Change 'A' to N'A' and the limit is reduced to 4000 bytes (since NVARCHAR has a maximum length of 4000 rather than 8000 characters)
IF HASHBYTES('SHA1', REPLICATE(N'A', 4000)) = HASHBYTES('SHA1', REPLICATE(N'A', 8000))
PRINT 'yes.';
ELSE
PRINT 'no.';



3. Use explicit CAST to use VARCHAR(MAX) and the 8000 byte limit disappears as snow in the sun (even though it does raise a warning - becaused the result of the HASHBYTES algorithm now exceeds 8000 bytes, but the defined return type of HASHBYTES is VARBINARY(8000)):
EDIT: Disregard this example. Apparently, there IS an 8000-byte limit on the input of HASHBYTES (but not on REPLICATE!), as pointed out by vk-kirov (see his message below).
IF HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)) = HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 8000))
PRINT 'yes.';
ELSE
PRINT 'no.';



4. Generic proof that string functions like REPLICATE and LEN do work with VARCHAR(MAX) strings longer than 8000 characters:
SELECT LEN(REPLICATE(CAST('A' AS varchar(max)), 9000));


(The error message posted by ziangij indicates that (s)he used the depricated data type TEXT instead of VARCHAR(MAX). The TEXT data type did indeed have many limitations, which is only one of the many good reasons to replace it with VARCHAR(MAX) - the other good reason being that TEXT will be removed in a future version of SQL Server).


(edit - added a warning to disregard example 3, after being corrected by vk-kirov)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
ziangij
ziangij
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3267 Visits: 377
thanks Hugo for your comments.
P.S. i am male... :-)
malleswarareddy_m
malleswarareddy_m
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2345 Visits: 1189
Nice question.Its new for me

Malleswarareddy
I.T.Analyst
MCITP(70-451)
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3890 Visits: 4408
Hugo Kornelis (2/1/2010)
the result of the HASHBYTES algorithm now exceeds 8000 bytes

No, it doesn't :-) The SHA-1 algorithm always produces 160-bit output, here is the proof: http://en.wikipedia.org/wiki/SHA#Comparison_of_SHA_functions.

The warning is raised because allowed input values for the HASHBYTES function are limited to 8000 bytes (http://msdn.microsoft.com/en-us/library/ms174415.aspx). I consider this as some strange obsolete limitation which can hardly be explained for MSSQL 2005 & 2008.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13111 Visits: 12151
vk-kirov (2/1/2010)
Hugo Kornelis (2/1/2010)
the result of the HASHBYTES algorithm now exceeds 8000 bytes

No, it doesn't :-) The SHA-1 algorithm always produces 160-bit output, here is the proof: http://en.wikipedia.org/wiki/SHA#Comparison_of_SHA_functions.

The warning is raised because allowed input values for the HASHBYTES function are limited to 8000 bytes (http://msdn.microsoft.com/en-us/library/ms174415.aspx). I consider this as some strange obsolete limitation which can hardly be explained for MSSQL 2005 & 2008.

You are absolutely right, and my explanation is incorrect.

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!


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
vk-kirov
vk-kirov
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3890 Visits: 4408
Hugo Kornelis (2/1/2010)
The HASHBYTES does apparently indeed have an (undocumented!!) limit of 8000 bytes on it's input string.

SET CAPTIOUS_MODE ON


To be fair, it is documented in BOL 2008 (http://msdn.microsoft.com/en-us/library/ms174415.aspx):
Remarks

Allowed input values are limited to 8000 bytes.


It is indeed undocumented in BOL 2005.
SET CAPTIOUS_MODE OFF


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search