## 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

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

we 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.

The 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.

The 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.

hi , how about

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

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 🙂

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