Home > Uncategorized > BINARY_CHECKSUM vs HASHBYTES in SQL

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.

 

About these ads
Categories: Uncategorized
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: