Posted by Gert E.R. Drapers on 06/03/05 01:29

Checksums do not guarantee unique values for different string, it is a
rotating add algorithm, since it is based on XOR ing values and only returns
a very limited set of possible values (the range of int), so you will get
collisions. You will also find that the amount of collisions it is very
depending on the collation sequence used. This is why SQL Server 2005
introduces the HashBytes function.

GertD@SQLDev.Net

Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright � SQLDev.Net 1991-2005 All rights reserved.

"Liam Caffrey" <liam.caffrey@gmail.com> wrote in message
news:1117749763.058901.177080@g49g2000cwa.googlegroups.com...
> Hi,
>
> It appears that binary_checksum can give the same checksum for
> different strings, which is a bit worrying. (I guess the algorithm is
> the problem in the context of a repeating pattern.)
>
> e.g.
> select binary_checksum('A')
> ,binary_checksum('AAAAAAAAAAAAAAAAA')
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
>
> ,binary_checksum('AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA')
>
> My question...
> Is this approach to generating checksums adequate for managing the
> object scripts in the SQL Server to ensure that they haven't changed. I
> guess that the probability of somebody making a change to a script and
> ending up with the same checksum is almost negligible. Has anybody used
> this approach in an FDA validated production environment, i.e. 'no ifs,
> no buts'? Would it stand up to scrutiny?
>
> Any experiences, thoughts?
>
> Regards
>
> Liam
>

[Back to original message]