Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Comparing a hash Expand / Collapse
Author
Message
Posted Saturday, January 30, 2010 10:45 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, January 19, 2011 12:15 PM
Points: 33, Visits: 40
Comments posted to this topic are about the item Comparing a hash
Post #856753
Posted Saturday, January 30, 2010 10:50 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,003, Visits: 369
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)...
Post #856756
Posted Sunday, January 31, 2010 9:17 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:56 AM
Points: 3,924, Visits: 1,607
Huh.....never used it. Ever. something new to think.

Thanks for the question.


SQL DBA.
Post #856910
Posted Sunday, January 31, 2010 10:17 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 4:53 AM
Points: 2,366, Visits: 1,844
Knew the right answer. Clicked on the wrong option by mistake.

"Keep Trying"
Post #856932
Posted Monday, February 1, 2010 1:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 5,969, Visits: 8,224
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
Post #856988
Posted Monday, February 1, 2010 2:32 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,003, Visits: 369
thanks Hugo for your comments.
P.S. i am male...
Post #857002
Posted Monday, February 1, 2010 2:34 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
Nice question.Its new for me


Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #857004
Posted Monday, February 1, 2010 3:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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.
Post #857019
Posted Monday, February 1, 2010 3:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:28 PM
Points: 5,969, Visits: 8,224
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
Post #857026
Posted Monday, February 1, 2010 5:53 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
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

Post #857092
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse