SQL SERVER – Introduction to BINARY_CHECKSUM and Working Example

In one of the recent consultancy, I was asked if I can give working example of BINARY_CHECKSUM. This is usually used to detect changes in a row. If any row has any value changed, this function can be used to figure out if the values are changed in the rows. However, if the row is changed from A to B and once again changed back to A, the BINARY_CHECKSUM cannot be used to detect the changes. Let us see quick example of the of same.

Following example is modified from the original example taken from BOL.

USE AdventureWorks;
GO
-- Create table
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[myTable]') AND TYPE IN (N'U'))
DROP TABLE [dbo].[myTable]
GO
CREATE TABLE myTable (column1 INT, column2 VARCHAR(256));
GO
-- Insert Value
INSERT INTO myTable VALUES (1, 'test');
GO
-- Check the Checksum
SELECT BINARY_CHECKSUM(*) AS BCH FROM myTable;
GO
-- Change the value
UPDATE myTable SET column2 = 'TEST';
GO
-- Check the Checksum
SELECT BINARY_CHECKSUM(*) AS BCH FROM myTable;
GO
-- Change the value
UPDATE myTable SET column2 = 'test';
GO
-- Check the Checksum
SELECT BINARY_CHECKSUM(*) AS BCH FROM myTable;
GO
-- Clean up
DROP TABLE [dbo].[myTable]
GO

Let us check the resultset here.

You can clearly see when the values are change the value of the BINARY_CHECKSUM is changed as well, however, if the value is changed back to original value the BINARY_CHECKSUM is restored. Are you using this feature in your application, if yes, I am interested to know where and when do you use it.

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

13 thoughts on “SQL SERVER – Introduction to BINARY_CHECKSUM and Working Example

  1. Hi Pinal
    Thanks for the post . I am avid follower of Sqlauthority blog.

    Here is my two cents on BINARY_CHECKSUM :)
    It can be used in transactional /merge replication .
    There are two methods to identify differences between subscriber and publisher
    1) row count
    Row count – counts the number of rows but doesn’t detect content differences between subscriber and Publisher.

    2 ) Row count and BINARY_CHECKSUM

    It counts the number of rows and also detects content differences between subscriber and Publisher.
    However this validation is very resource intensive and should be executed sparingly .

    Regards
    Chetana Pretham

  2. Thanks pinal its very informative . Also wants to know whether using BINARY_CHECKSUM or any other function can we find which column(s) has been changed.

    Regards,
    Nishit

    • I would think CDC should be used for column changes as opposed to Binary Check sum. Am I right, does anyone have a thought as to which one is better for column changes?

      • CDC is used mainly by SSIS (integration) and SSAS (analysis – cubes) as far as I know whereas binary check sum is used for optimistic locking and concurrency collision detection when table(s) do(es) not have timestamp field(s). In short: a) CDC runs in the background and the tables it yields are used by SSIS and SSAS; b) binary check sum are added as extra computed columns to select statements used by ado.net data adapters to fill ado.net datasets/datatables. Then the dataadapters shall have their update and delete commands updated so that their SQL statements use the binary check sum in the where clause for optmistic locking and concurrency collision detection.

  3. Hello!

    Great article, Pinal. Very simply demonstrates the concept of Binary checksum and also it’s pros & cons.

    We use the binary checksum in our application for merge replication (in fact, ours has a custom filter; but conceptually, it’s merge replication). We use the binary checksum only to check for row-count and checksum violations. Because most of our tables have audit records, even if the record circum-vented the replication filters, it will show up as a violation.

    That’s where I have seen a few other products use binary check-sums as well. A few use these for auditing, but now I realize that someone can easily go untraced by resetting the original value.

    Have a great week ahead!

  4. Hi Pinal,

    My company uses BINARY_CHECKSUM to compare snapshots of data in an application that integrates our different systems. We aren’t too worried about auditing the values, i.e. if they change back to their original values, we only need the latest changed data at a set time, so BINARY_CHECKSUM works quite well for us.

    Lee.

    • The following also returns the same Binary_Checksum values.
      select BINARY_Checksum(’1044 ZONE 2′)
      select BINARY_Checksum(’1074 ZONE 1′)

  5. Hi Pinal

    We’ve found a bit of a potential problem with BINARY_CHECKSUM(), perhaps we are missing something but could you explain if this is intended behavior or a bug?

    – these should all be different and are
    SELECT BINARY_CHECKSUM(‘ABCDEF-ABCDEF’)
    SELECT BINARY_CHECKSUM(‘abcdef-abcdef’)

    – add one character to each string that is separated by a ‘-’ character
    – these should be different you would imagine
    – however if we have 7 or more characters and the same number of characters on each side they are the same
    SELECT BINARY_CHECKSUM(‘ABCDEFG-ABCDEFG’)
    SELECT BINARY_CHECKSUM(‘abcdefg-abcdefg’)

    – same effect with any other character upper case or not or alphanumerical (same ‘split’ character and case though!)
    SELECT BINARY_CHECKSUM(‘ABCDEFGxABCDEFG’)
    SELECT BINARY_CHECKSUM(‘abcdefgxabcdefg’)

    – now try 7 characters without the ‘-’ character and all is ok
    SELECT BINARY_CHECKSUM(‘ABCDEFGABCDEFG’)
    SELECT BINARY_CHECKSUM(‘abcdefgabcdefg’)

    – or put 2 ‘-’ characters in and all is ok!
    SELECT BINARY_CHECKSUM(‘ABCDEFG–ABCDEFG’)
    SELECT BINARY_CHECKSUM(‘abcdefg–abcdefg’)

    Paul

    • The following also returns the same Binary_Checksum values.
      select BINARY_Checksum(’1044 ZONE 2′)
      select BINARY_Checksum(’1074 ZONE 1′)

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #040 | Journey to SQL Authority with Pinal Dave

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