Post reply

Checksum hashvalue in sql 2012

  • February 11, 2015 at 8:46 pm

    #301022

    Hi ,

    By using below command I am able to get the hashvalue,

    update ArchiveBBxCcsms

    set RowChecksum=HashBytes('MD5', CAST(CHECKSUM(Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008,Col009,Col010,Col011,Col012,Col013,Col014,Col015,Col016,Col017,Col018,Col019,Col020,Col021,Col022,Col023,Col024,Col025,Col026,Col027,Col028,Col029,Col030,Col031,Col032,Col033,Col034) AS VARCHAR(max)))

    --where sqlid=67427991

    but when I am updating the entire table I am getting different hash value like

    when I am updating single row my output will be

    0xB084CB4D78EBC3ACF2BFB2CD373DE4E6

    And when I am updating entire table I am getting below hashvalue

    蒰䷋곃뿲춲㴷

    please correct me

    thanks

  • February 11, 2015 at 10:26 pm

    #1777131

    Quick thought, it makes no sense to use CHECKSUM inside the HASHBYTES function, change CHECHSUM to CONCAT to avoid collisions, consider this example:

    😎

    SELECT CONVERT(VARCHAR(34),HashBytes('MD5', CONCAT(Col001,Col002,Col003,Col004,Col005,Col006,Col007,Col008

    ,Col009,Col010,Col011,Col012,Col013,Col014,Col015,Col016

    ,Col017,Col018,Col019,Col020,Col021,Col022,Col023,Col024

    ,Col025,Col026,Col027,Col028,Col029,Col030,Col031,Col032,Col033,Col034)),1)

  • February 11, 2015 at 10:34 pm

    #1777133

    Thanks sir ,

    its working now

  • February 11, 2015 at 10:53 pm

    #1777135

    You are very welcome.

    😎

    A word of caution, make certain you understand the shortcomings of this code, mainly it neither handles NULL values nor does it guarantee unique values for all combinations of columnar values. The latter can be addressed by adding a delimiter character between the columns in the concatenation, consider this example:

    COL01 COL02 CONCAT_COL

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

    100 10 10010

    10 010 10010

    The addition of a delimiter produces unique values

    COL01 DELIMITER COL02 CONCAT_COL

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

    100 | 10 100|10

    10 | 010 10|010

    The previous code will then look like this

    SELECT CONVERT(VARCHAR(34),

    HashBytes('MD5',

    CONCAT(Col001,CHAR(124),Col002,CHAR(124),Col003,CHAR(124),Col004,CHAR(124),Col005,CHAR(124),Col006,CHAR(124),Col007,CHAR(124),Col008

    ,CHAR(124),Col009,CHAR(124),Col010,CHAR(124),Col011,CHAR(124),Col012,CHAR(124),Col013,CHAR(124),Col014,CHAR(124),Col015,CHAR(124),Col016

    ,CHAR(124),Col017,CHAR(124),Col018,CHAR(124),Col019,CHAR(124),Col020,CHAR(124),Col021,CHAR(124),Col022,CHAR(124),Col023,CHAR(124),Col024

    ,CHAR(124),Col025,CHAR(124),Col026,CHAR(124),Col027,CHAR(124),Col028,CHAR(124),Col029,CHAR(124),Col030,CHAR(124),Col031,CHAR(124),Col032

    ,CHAR(124),Col033,CHAR(124),Col034)),1)

  • February 12, 2015 at 4:47 am

    #1777184

    Another word of caution, as long as you have numeric values in your columns then it will work. But what if you have Alphabet in your data.

    Consider the following scenario:

    cola colb

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

    a b

    A b

    a B

    A B

    As per understanding, these all values are a,b then hash should be the say ... but no that is not the case.

    Declare @table Table ( cola varchar(20), colb varchar(20))

    insert into @table

    select 'a', 'b' union all

    select 'A', 'b' union all

    select 'a', 'B' union all

    select 'A', 'B'

    select *,

    HashBytes('MD5', CONCAT(cola, colb))

    from @table

    You will get different hash value for each combination because HASHBYTE function is default case sensitive. To resolve this issue using either UPPER or LOWER string function available.

    Something like this:

    select cola

    , colb

    , Convert(varbinary(16),HashBytes('MD5', CONCAT(cola, colb))) AS WithoutUsingUpperCaseFunction

    , Convert(varbinary(16),HashBytes('MD5', CONCAT(UPPER(cola), UPPER(colb)))) AS WithUsingUpperCaseFunction

    , Convert(varbinary(16),HashBytes('MD5', CONCAT(LOWER(cola), LOWER(colb)))) AS WithUsingLowerCaseFunction

    from @table

    Final result set will be

    cola colb WithoutUsingUpperCaseFunction WithUsingUpperCaseFunction WithUsingLowerCaseFunction

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

    a b 0x187EF4436122D1CC2F40DC2B92F0EBA0 0xB86FC6B051F63D73DE262D4C34E3A0A9 0x187EF4436122D1CC2F40DC2B92F0EBA0

    A b 0x0E4C46DF226B9C0CB391311C54F28EFE 0xB86FC6B051F63D73DE262D4C34E3A0A9 0x187EF4436122D1CC2F40DC2B92F0EBA0

    a B 0x081B1F3A41C98110994E1B45A4A886EE 0xB86FC6B051F63D73DE262D4C34E3A0A9 0x187EF4436122D1CC2F40DC2B92F0EBA0

    A B 0xB86FC6B051F63D73DE262D4C34E3A0A9 0xB86FC6B051F63D73DE262D4C34E3A0A9 0x187EF4436122D1CC2F40DC2B92F0EBA0

    Hope it helps

Viewing 5 posts - 1 through 4 (of 4 total)

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