I don't think you should state that checksum etc shouldn't be used; hashsum may be a better solution but it's still a hash and still runs the risk of collision.

Of course I meant hashBYTES may be a better solution .....

Something else that can help is to send the length of the data with the hash, less chance (but not zero chance) two pieces of data having the same hash and the same length.

Matt - I ran some really simple (unscientific) tests of HashBytes SHA-1 versus CHECKSUM against a "real" data set (the list of the most common names in the United States from the U.S. Census Bureau).  HashBytes SHA-1 generated zero collisions.  CHECKSUM generated collisions as frequently as 1 in 10 (depending on the combinations of data).  The odds of an SHA-1 collision are on the order of 1 in 2^160 (roughly about 1 in 10,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000).  As Steve demonstrated the CHECKSUM function "repeats" every 16 characters.

Chris, try this:

SELECT CHECKSUM('LE'), LEN('LE')

SELECT CHECKSUM('MU'), LEN('MU')

As you can see the length of the string isn't a big help with CHECKSUM. For more examples try this one:

WITH X

AS

(

SELECT 'A' AS C

UNION ALL

SELECT 'B'

UNION ALL

SELECT 'C'

UNION ALL

SELECT 'D'

UNION ALL

SELECT 'E'

UNION ALL

SELECT  'F'

UNION ALL

SELECT  'G'

UNION ALL

SELECT  'H'

UNION ALL

SELECT  'I'

UNION ALL

SELECT  'J'

UNION ALL

SELECT  'K'

UNION ALL

SELECT  'L'

UNION ALL

SELECT  'M'

UNION ALL

SELECT  'N'

UNION ALL

SELECT  'O'

UNION ALL

SELECT  'P'

UNION ALL

SELECT  'Q'

UNION ALL

SELECT  'R'

UNION ALL

SELECT  'S'

UNION ALL

SELECT  'T'

UNION ALL

SELECT  'U'

UNION ALL

SELECT  'V'

UNION ALL

SELECT  'W'

UNION ALL

SELECT  'X'

UNION ALL

SELECT  'Y'

UNION ALL

SELECT  'Z'

)

SELECT X1.C + X2.C, CHECKSUM(X1.C + X2.C)

FROM X X1

CROSS JOIN X X2

ORDER BY CHECKSUM(X1.C + X2.C)

Try substituting HashBytes for CHECKSUM in the above and you won't see a single collision.

It's Michael's book, so he posted his code, and this is a good reason to buy it. You'll learn some interesting things.

I think one simple possible solution to the collisions would be calculating 2 HASHBYTES - one by SHA-1 and another by SHA-2 algorithms.

Then one can either compare both pairs of HASHBYTES or get just one string that is some of those 2 functions.

I think the chance that 2 different strings would get the same 2 HASHBYTES are close to 0.

Correction to previous post - there should only be 48 zeroes behind that 1, not 49.  Still really low odds of a collision.

Mark - 1:10^48 is pretty close to zero, but some people do go with the "belt-and-suspenders" method and apply multiple hashes.  The HashBytes function doesn't natively support SHA-2, but you could use MD5 as the second hash or get SHA-2 functionality through SQL CLR.

One thing not mentioned in the article is the support for binary_checksum(*).

This could be used to readily detect changes on a row without coding every column. (It could if it was reliable anyway).

Does anyone know how to get this kind of programming shortcut with HashBytes?

@Renato: The naive approach would be to simply cast all columns to a binary datatype, concatenate then together and pass that to HashBytes(). It should work fine, but performance may not be very good. Test and see :-)

1) RSA Labs says that SHA-1 is more 'collision-free' than MD5

2) RSA Labs says Researchers (1994) brute force attach estimate a collision using MD5 on avg after 24 days running on a $10M system.

What i have not seen explained by RSA labs or here is what scenario will result in the collision:

a) 2 radically different strings

b) 2 very similar strings

The reason i want to know is that if the answer is (a) and not (b) then i can use Hashbytes as the technique for recognising change of none key columns of a row for an ETL load. BUT if (b) is the problem, and by definition the values wil mostly be similar will the weight the result to creating frequent collisions?