Checksum hashvalue in sql 2012

Question

Checksum hashvalue in sql 2012

vipin_jha123

Hall of Fame

Points: 3085
More actions
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

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

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

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 1

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)

vipin_jha123 Hall of Fame Points: 3085 More actions · Answer 2

vipin_jha123

Hall of Fame

Points: 3085

February 11, 2015 at 10:34 pm

#1777133

Thanks sir ,

its working now

Eirikur Eiriksson SSC Guru Points: 182915 More actions · Answer 3

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)

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

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