#0362 – SQL Server – Change Detection in Microsoft SQL Server – Limitations of T-SQL: BINARY_CHECKSUM and CHECKSUM


Identification of changes made to the data in a system is an important aspect of data storage design and data cleanup/quality improvement activities. For most enterprise systems, the need to implement change detection is driven by some sort of auditing requirements. A couple of years ago, I authored a series of articles on SQLServerCentral.com and on this blog around data change and tamper detection mechanisms available in Microsoft SQL Server. These are:

  • An in-depth look at change detection in SQL Server – Part 01 [Link]
  • An in-depth look at change detection in SQL Server – Part 02 [ Link]
  • HASHBYTES: Is CHECKSUM really required? [Link]
  • HASHBYTES-String or binary data would be truncated: Msg 8152 [ Link]

A recent coincidence at work prompted me to write this post. I was working on comparing a set of records from one table to another after a data cleanup exercise when I realized that a couple of my checksums were coming up as 0, i.e. a blank string (as disccussed in my article on Chage Detection, part 01). The twist to the tale was that there were no blank strings in the sample that I was using.

The Problem

In order to demonstrate the issue clearly, I have prepared the following sample. As can be seen from the sample, both CHEKSUM and BINARY_CHECKSUM work as expected as long as the string under evaluation is less than 26,000 characters in length. As soon as the string hits the 26,000 mark, the functions stop working.

USE tempdb;
GO

DECLARE @stringPatternToReplicate VARCHAR(MAX) = 'a'
DECLARE @stringPatternToEvaluate VARCHAR(MAX)
DECLARE @replicateTillLength INT = 25999
 
SELECT @stringPatternToEvaluate = REPLICATE(@stringPatternToReplicate,@replicateTillLength);
 
SELECT LEN(@stringPatternToEvaluate) AS StringLength,
       CHECKSUM(@stringPatternToEvaluate) AS CheckSumForString,
       BINARY_CHECKSUM(@stringPatternToEvaluate) BinaryCheckSumForString,
       @stringPatternToEvaluate AS StringPatternToEvaluate;

--Repeat after incrementing the @replicateTillLength by 1
SELECT @replicateTillLength += 1
 
SELECT @stringPatternToEvaluate = REPLICATE(@stringPatternToReplicate,@replicateTillLength);
 
SELECT LEN(@stringPatternToEvaluate) AS StringLength,
       CHECKSUM(@stringPatternToEvaluate) AS CheckSumForString,
       BINARY_CHECKSUM(@stringPatternToEvaluate) BinaryCheckSumForString,
       @stringPatternToEvaluate AS StringPatternToEvaluate;
GO

Solution?

The quick solution that I moved ahead with was to perform a direct comparison of the strings involved.

Now, we know that CHECKSUM and BINARY_CHECKSUM will not work if the datatype being evaluated is one of: text/ntext/image/cursor/xml. But, in the example provided above, the strings were the classic – VARCHAR with the MAX keyword to allow storage > 8000 characters.

However, I would like to invite views from you, the kind reader on whether you have faced a similar issue in the past or whether you have any other ideas to resolve this issue.

Summary:

Checksum and BINARY_CHECKSUM can fail to detect a change if:

  • The characters involved are not standard ASCII characters, i.e. have an ASCII value greater than 255
  • The string is a blank string
  • The string is more than 25,999 characters in length (as demonstrated above)

Open Item

I would like to invite views from you, the kind reader on whether you have faced a similar issue in the past or whether you have any other ideas to resolve this issue.

I have written up a Microsoft Connect ticket for this issue to look for an official explanation [MS Connect item #2021430].

Further Reading

  • An in-depth look at change detection in SQL Server – Part 01 [Link]
  • An in-depth look at change detection in SQL Server – Part 02 [ Link]
  • HASHBYTES: Is CHECKSUM really required? [Link]
  • HASHBYTES-String or binary data would be truncated: Msg 8152 [ Link]

Until we meet next time,
Be courteous. Drive responsibly.

Advertisements

Let me know what you think about this post by leaving your feedback here!

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