byte representation in BINARY_CHECKSUM()?

Question

according to this article

http://decipherinfosys.wordpress.com/2007/05/18/checksum-functions-in-sql-server-2005/

they say that

BINARY_CHECKSUM() returns the same value if the elements of two expressions have the same type and byte representation. So, “2Volvo Director 20″ and “3Volvo Director 30″ will yield the same value

my question is what is byte representation?

and why

SELECT BINARY_CHECKSUM('2Volvo Director 20'  )// -1356512636
SELECT BINARY_CHECKSUM('3Volvo Director 30'  )// -1356512636

gives the same results ?

the byte of '2' is not as the byte of '3'

AakashM · Accepted Answer · 2011-09-22 13:56:28Z

5

I don't know exactly what that article means by that phrasing, but from googling around I see that a SQL Server MVP has reverse-engineered the full algorithm (eg here) and others have noticed that the algorithm has weird problems around a 16-character cycle. Notice that in that sample, the changes occur at position 1 and position 17:

SELECT BINARY_CHECKSUM('2Volvo Director 20'  )-- -1356512636
SELECT BINARY_CHECKSUM('3Volvo Director 30'  )-- -1356512636
--                      12345678901234567

which are 16 apart - if the space is removed so the changes occur at positions 1 and 16, you get different checksums:

SELECT BINARY_CHECKSUM('2Volvo Director20'  )-- 1257395465
SELECT BINARY_CHECKSUM('3Volvo Director30'  )-- 1257395480
--                      12345678901234567

The internet consensus appears to be that the hash function offered by BINARY_CHECKSUM is of low quality, and that you should use HASHBYTES in preference.

answered Sep 22, 2011 at 13:56

AakashM

63.5k17 gold badges154 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Royi Namir Over a year ago

so...why they invented this if its not accurate ? or have some black holes in it ( the 16' cycle...)

AakashM Over a year ago

We all make mistakes, even Microsoft :) In particular, coming up with good hash functions is extremely hard, whereas coming up with bad hash functions is extremely easy. At least the docs for CHECKSUM say (basically) "don't use it".

nav Over a year ago

SELECT BINARY_CHECKSUM('fa') SELECT BINARY_CHECKSUM('dA') both yield same values... 1537

Collectives™ on Stack Overflow

byte representation in BINARY_CHECKSUM()?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related