Comparing a hash

  • alastors

    SSC Enthusiast

    Points: 155

    Comments posted to this topic are about the item Comparing a hash

  • ziangij

    SSCertifiable

    Points: 7068

    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

    SSChampion

    Points: 13157

    Huh.....never used it. Ever. something new to think.

    Thanks for the question.

    SQL DBA.

  • ChiragNS

    One Orange Chip

    Points: 26137

    Knew the right answer. Clicked on the wrong option by mistake. πŸ˜‰

    "Keep Trying"

  • Hugo Kornelis

    SSC Guru

    Points: 64675

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • ziangij

    SSCertifiable

    Points: 7068

    thanks Hugo for your comments.

    P.S. i am male... πŸ™‚

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Nice question.Its new for me

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • vk-kirov

    SSCertifiable

    Points: 7686

    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

    SSC Guru

    Points: 64675

    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/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • vk-kirov

    SSCertifiable

    Points: 7686

    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

  • William Vach

    SSCarpal Tunnel

    Points: 4825

    Good question! I learned something today.

  • SQLRNNR

    SSC Guru

    Points: 281243

    Thanks. I, too, learned something new today.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Now this one still bothers me...

    Why does it execute the no part after the 8125 error?

    By the way, it only executes it with the following SET options:

    SET XACT_ABORT OFF

    SET ANSI_WARNINGS ON

    Funny enough, one would think that if the warnings are on, the error would stop execution, but instead you need to turn warnings off to make SQL Server stop the execution. And unfortunately BOL does not really assist in understanding this.

    At least the XACT_ABORT setting makes some kind of sense with regards to the error.

    Best Regards,

    Chris BΓΌttner

  • Saurabh Dwivedy

    SSCommitted

    Points: 1881

    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

    Be Happy!
  • john.arnott

    SSChampion

    Points: 11882

    Hugo Kornelis (2/1/2010)


    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.

    This piqued my curiosity. Could the "no" result from your direct test

    IF HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000))

    is NULL

    PRINT 'yes.';

    ELSE

    PRINT 'no.';

    ...be because the result of the IF test is NULL? When the HASHBYTES() function fails with the input truncation error, it does not return NULL, but rather doesn't return anything. Then, the IF statement executes and doesn't find a NULL, so returns FALSE, which of course leads to the "Print 'No'" branch.

    Running in SSMS with results to text makes it a bit easier to see because it says "NULL" for null results and nothing for the failed HASHBYTES().

    select IsNull(HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 9000)),0x2222aaaa9999)

    select HASHBYTES('SHA1', REPLICATE(CAST('A' AS varchar(max)), 4000))

    Declare @MyNull varbinary

    select @myNull

    Returns

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    Msg 8152, Level 16, State 10, Line 1

    String or binary data would be truncated.

    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    0xD5FE2200837AB7B4FB073787F8E531D6C6A8CDD2

    (1 row(s) affected)

    ----

    NULL

    (1 row(s) affected)

Viewing 15 posts - 1 through 15 (of 23 total)

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