Home > SQL Server > Checksum and Checksum function – SQL Server

Checksum and Checksum function – SQL Server


Checksum is fixed length computed value. It is used to detect the accidental errors in the data transmission.

Usually, an algorithm is used to compute the checksum value and each algorithm is designed for a specific goal.

Checksum functions or algorithms are related to hash functions, fingerprints, randomization functions, and cryptographic hash functions

Few Checksum algorithms

Checksum() Function – SQL Server

This hash function is used to compute checksum value for an entire row or list of expressions or columns. It returns a computed integer value.

Checksum arguments are * to compute checksum value for entire row or specific columns or expressions separated by comma with valid data types.

Invalid data types are text, ntext, image, XML, and cursor, and also sql_variant.

Implementation

0. Compute checksum for NULL value.

pic1

1. Compute checksum using expression

Select checksum(‘SQL’, ‘Server’, ‘Rider’)

Output: -1450562993

2. Compute checksum using specific columns in Adventureworks table.

pic13. Compute checksum for entire row

pic2This checksum function can be used in data warehouse to detect change in a row. But, the checksum() may return same value for two different arguments. Because, checksum() algorithm will compute few bits in a byte and gives us only 4 byte (integer) value from for the whole data. So, the possibilities of getting collision or duplicates is high.

Actually, CHECKSUM is intended for use in building hash indexes in SQL Server.

This issue has been explained very clearly in another blog. You can refer CHECKSUM weakness explained.

SQL Server has other hash function such as CHECKSUM_AGG (),HASHBYTES () and
BINARY_CHECKSUM (). I will explain about these functions in my next blog post.

Advertisements
  1. sreedhar reddy
    November 9, 2015 at 5:09 am

    nice

  1. February 25, 2013 at 11:36 pm

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

%d bloggers like this: