• Has anyone come across cases where SHA1 has produced output longer than 20 bytes, because I think I have. We are using HASHBYTES to detect changes in our data warehouse, and we were getting type 2 changes, even though all the columns values were exactly the same between the most current and previous rows; even the hashbyte value looked the same!

    But, upon looking closer, the HASHBYTE function was returning this: 0xEE0816119692A1C4DCC2045EA1E37CD47EFF4900

    But, the value in our varbinary(20) column in the table was storing this: 0xEE0816119692A1C4DCC2045EA1E37CD47EFF49

    Notice the two zeros at the end of the function's output. For some reason, when SQL writes the output from the function to the table it truncates those last two "characters" at the end!

    We have no idea why it does that, but when we increase the varbinary column in the table from 20 to 40, SQL doesn't truncate the output anymore, and all is well.

    Anyway, if anyone has any insight on why SQL seems to be truncating the output from the function as it writes to the varbinary(20) column, please let me know, I'm curious!

    L' Eomot Inversé (4/26/2011)


    wta306 (4/25/2011)


    How do you determine what size to make your VARBINARY field to hold the results of the HASHBYTES function?

    Is it simply a matter of setting it to be equal to the largest HashByte function input value?

    DECLARE @vHashResults AS VARBINARY(40)

    DECLARE @vHashInput01 AS CHAR(20)

    DECLARE @vHashInput02 AS CHAR(20)

    SET @vHashInput01 = 'thisisa20bytechar123'

    SET @vHashInput02 = 'thisisa21bytechar1234'

    SET @vHashResults = HashBytes('SHA1', @vHashInput01 + @vHashInput02)

    I would be somewhat horrified if HashBytes('SHA1', <anything>) delived a result with any length other than 160 bits (20 bytes)! That's what MS thinks it delivers too, according to BoL.