BINARY_CHECKSUM vs HASHBYTES in SQL
If you need to create a short checksum or hash code in SQL, you can use CHECKSUM, BINARY_CHECKSUM or HASHBYTES.
HASHBYTES is the best for ensuring lower collisions, whereas BINARY_CHECKSUM is worst. But for performance, here is a comparison on a 1 million row table.
declare @timeStart as datetime
set @timeStart = getdate()
select MAX(hashbytes(‘MD5’,x)) from y
select datediff(ms,@timestart,getdate())checksum = 370,363,370,410,356 = 373 ms
binary_checksum = 403,356,360,390,356 = 373 ms
MD5 = 2083, 1976, 1743 , 1746 , 1753 = 1860 ms
Therefore, Binary_Checksum is 5 times faster than MD5 HASHBYTES, and the same speed as checksum.
So, my advice,
Iif you need speed over accuracy – use CHECKSUM, not BINARY_CHECKSUM.
If you need accuracy over speed – use HASHBYTES.
