CHECKSUM , produces same hash for two different inputs. is this right?

Hi,

We are using binary_checksum in some of instead of update trigger. The problem came into the knowledge when update falied without raising any error. We came to know after research that checksum returns same number for two different inputs and thats why update failed.

We are using following type of inside the trigger.

UPDATE [dbo].[Hospital]

SET

[HospitalID]= I.[HospitalID],

[Name]= I.[Name],

[HospitalNumber]= I.[HospitalNumber],

[ServerName] = I.[ServerName],

[IsAuthorized]= I.[IsAuthorized],

[IsAlertEnabled]= I.[IsAlertEnabled],

[AlertStartDate]= I.[AlertStartDate],

[AlertEndDate]= I.[AlertEndDate],

[IsTraining]= I.[IsTraining],

[TestMessageInterval]= I.[TestMessageInterval],

[DelayAlertTime]= I.[DelayAlertTime],

[IsDelayMessageAlert]= I.[IsDelayMessageAlert],

[IsTestMessageAlert]= I.[IsTestMessageAlert],

[IsUnAuthorizedMessageAlert]= I.[IsUnAuthorizedMessageAlert],

[IsWANDownAlert]= I.[IsWANDownAlert],

[IsWANUpAlert]= I.[IsWANUpAlert],

[CreateUserID]= Hospital.[CreateUserID],

[CreateWorkstationID]= Hospital.[CreateWorkstationID],

[CreateDate]= Hospital.[CreateDate] ,

/* record created date is never updated */

[ChangeUserID]= suser_name(),

[ChangeWorkstationID]= host_name(),

[ChangeDate]= getdate() ,

/* Updating the record modified field to now */

[CTSServerID]= I.[CTSServerID]

FROM inserted i

WHERE

i.[HospitalID]= Hospital.[HospitalID]

AND binary_checksum(

Hospital.[HospitalID],

Hospital.[Name],

Hospital.[HospitalNumber],

Hospital.[ServerName],

Hospital.[IsAuthorized],

Hospital.[IsAlertEnabled],

Hospital.[AlertStartDate],

Hospital.[AlertEndDate],

Hospital.[IsTraining],

Hospital.[TestMessageInterval],

Hospital.[DelayAlertTime],

Hospital.[IsDelayMessageAlert],

Hospital.[IsTestMessageAlert],

Hospital.[IsUnAuthorizedMessageAlert],

Hospital.[IsWANDownAlert],

Hospital.[IsWANUpAlert]) !=

binary_checksum(

I.[HospitalID],

I.[Name],

I.[HospitalNumber],

I.[ServerName],

I.[IsAuthorized],

I.[IsAlertEnabled],

I.[AlertStartDate],

I.[AlertEndDate],

I.[IsTraining],

I.[TestMessageInterval],

I.[DelayAlertTime],

I.[IsDelayMessageAlert],

I.[IsTestMessageAlert],

I.[IsUnAuthorizedMessageAlert],

I.[IsWANDownAlert],

I.[IsWANUpAlert]) ;

Here is the checksum example which produces same results for two different input.

DECLARE @.V1 VARCHAR(10)

DECLARE @.V2 VARCHAR(10)

SELECT @.V1 = NULL, @.V2=NULL

SELECT binary_checksum('KKK','San Jose','1418','1418SVR ',0,1,@.V1,@.V2,0,30,180,1,0,1,1,1),

binary_checksum('KKK','San Jose','1418','1418SVR ',1,1,@.V1,@.V2,0,30,180,1,1,1,1,1)

Lookat the two binary_checksum above, they are different and should not match, but they both return same value.

Can someone please provide some info on these.

Did any one looked at this? I guess this is a very very critical. The checksum is used by storage engine to verify the page integrity and the checksum is stored in every page. if it is producing the same hash for two different inputs it may not verify page correctly.

|||CHECKSUMS ARE NOT UNIQUE. You cannot use checksums in the way you are trying to use them.

Check out BOL under BINARY_CHECKSUM

BINARY_CHECKSUM(*), computed on any row of a table, returns the same value as long the row is not subsequently modified. BINARY_CHECKSUM(*) will return a different value for most, but not all, changes to the row, and can be used to detect most row modifications.

and CHECKSUM

If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change. For this reason, we do not recommend using CHECKSUM to detect whether values have changed, unless your application can tolerate occasionally missing a change. Consider using HashBytes instead. When an MD5 hash algorithm is specified, the probability of HashBytes returning the same result for two different inputs is much lower than that of CHECKSUM.

Yes, you are correct, there is a small insignificant chance the checksum may be the same on a page even though the data has changed. In large binary data, like SQL server's 64k pages, this chance is in the range of 100 million to 1.

|||

Tom,

In these case the checksum is consistently produces same hash for two different inputs. So every time when I was updating the two columns specified above, it failed. So I thought there is a bug in the code which is not detecting the changes in the input.

Anyway, your suggestion to use HashBytes is very helpful.

Thanks,