Home > SQL Server > Detect changes in a row of a table using BINARY_CHECKSUM() – SQL SERVER

Detect changes in a row of a table using BINARY_CHECKSUM() – SQL SERVER


These days I am writing blog posts about the checksum functions. I have already discussed about checksum, checksum_agg functions in my previous blog post.

Today, I am going to explain the features of BINARY_CHECKSUM function.

Binary_checksum function is used to compute the checksum value of an entire row of a table or a list of expressions. This function is also used to detect the changes in a table.

Function usage:

  • Binary_checksum(*) is used to compute checksum value for the entire row.
  • Binary_checksum(expression1, 2,….,n) is used to compute checksum value for the given expression list.

Data types that are invalid to compute checksum value are  text, ntext, image, cursor, xml, and noncomparable common language runtime (CLR) user-defined types. So, we should not use these data types in the binary_checksum function.

This function returns an interger value. So, We can use this checksum value for the original row to compare with a current row to check whether there is any change in that particular row for a table.

Binary_checksum() and checksum() functions are have same functionality but the binary_checksum() returns different values for a string value with different case.

Implementation

1. Compute checksum value for the null value

pic1null is a noncomparable input. what if we use null value with type int.

pic2we have got a checksum value for the integer typed null value.

2. Compute checksum value for an expression or table columns with different order. I am using AdventureWorks2012.Production.Culture table in this example. I have two queries with expression list in different order.

pic3The checksum values for the two queries are different. So, we have learned that the order of the column in a table and expression should be same while detecting the changes.

3. Computing checksum value using binary_checksum() and checksum() function for a string with different case and compare the results.

pic4The result shows that the binary_checksum yields different results of a same string with different case. But, checksum() function gives same output for both case-sensitive and case-insensitive value of similar string.

Advertisements
  1. Valeriu
    December 30, 2013 at 7:05 am

    hi , how about

    select BINARY_CHECKSUM(0.11),BINARY_CHECKSUM(1.1) ? 🙂

    • February 10, 2014 at 11:07 am

      it is a collision. this is somehow known or developer should be aware of this during development. But, I have used this in some of my projects and have no problems yet 🙂

    • gp
      August 12, 2014 at 3:52 pm

      Collision happens because of implicit conversion to varchar. Cast values to numeric type, they will produce different B_checksum

  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

%d bloggers like this: