Saturday, May 25, 2013

Sql trivia: BINARY_CHECKSUM

You know how in SQL Books online the doco tells you to be wary of using CHECKSUM and BINARY_CHECKSUM functions because they will miss some updates. Here's a trivial example:

select 
 BINARY_CHECKSUM(A),
 BiggestInt,
 BINARY_CHECKSUM(BiggestInt)
from (
 select cast(null as int) as A,
 Power(cast(2 as bigint),31) -1 as BiggestInt
) x

Which returns 2147483647 in all 3 cases. So essentially BINARY_CHECKSUM is blind to the difference between a null and an int(max value). Which is fair enough... but does illustrate the point that even if you are only doing a checksum on a single nullable 4 byte field, you can't stuff it into 32 bits without getting at least one collision.

Popular Posts