Home > SQL Server > Compute checksum for a group of values using CHECKSUM_AGG – TSQL

Compute checksum for a group of values using CHECKSUM_AGG – TSQL


CHECKSUM_AGG() function is used to compute checksum value for a group of integer values. The return data type of this function is an int. This function ignores NULL values in the group and it can be used with OVER clause also.

We can compute checksum for a group with ALL values or DISTINCT values or an integer expression. ALL and DISTINCT are the keywords that can be place in front of the function argument to get the desire result.

Actually, this function can be used to detect the changes in a table.

Implementation

Compute checksum with all values in a table. Checksum computation before change

pic1Checksum computation with changes. So, it returns different checksum value than the previous output.

pic2ALL is the default argument. So, it computes checksum for the entire table. But, we are going to use DISTINCT keyword to compute checksum now.

pic3It is a useful function to detect the changes in a table. But, It may not detect the small changes and returns same output for different list of values. So, We have to use this function with extra care. For instance, swapping row values may not yield different checksum value.

Advertisements
  1. Vasudha
    August 17, 2015 at 6:10 am

    Please tell me how did we get the value 262/ 259 in the above examples

  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: