SQL Server has a CHECKSUM_AGG() function that allows us to get the checksum of the values in a group. This can be useful for detecting changes in the values in a column.

Syntax

The syntax goes like this:

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

So expression is a required argument, but we also have the option of specifying ALL or DISTINCT.

expression must be an int.

By default, ALL is used, which means that the function is applied to all values, including duplicates.

If we specify DISTINCT, then the function only returns the checksum of unique values.

We can also use an OVER clause with the CHECKSUM_AGG() function.

Example

Suppose we have the following table:

SELECT * FROM Dogs;

Result:

DogName  Activity     Score
-------  -----------  -----
Bruno    Fetch Stick  43   
Cooper   Fetch Stick  43   
Max      Fetch Stick  91   
Bruno    Wag Tail     65   
Cooper   Wag Tail     51   
Max      Wag Tail     87   
Bruno    Keep Quiet   2    
Cooper   Keep Quiet   null 
Max      Keep Quiet   1    

We can use the CHECKSUM_AGG() function against that table like this:

SELECT CHECKSUM_AGG( Score )
FROM Dogs;

Result:

125

NULL values are ignored when using CHECKSUM_AGG(). Our table contains a NULL value, and so it was ignored.

Updates to Data

If we update the data, we should see a change in the checksum returned by CHECKSUM_AGG().

Let’s update one of the values in the Score column:

UPDATE Dogs SET Score = 3 
WHERE DogName = 'Bruno' 
AND Activity = 'Keep Quiet';

Now let’s run CHECKSUM_AGG() again:

SELECT CHECKSUM_AGG( Score )
FROM Dogs;

Result:

124

The output value has changed.

The DISTINCT Keyword

In SQL, the DISTINCT clause can be used in various places to apply operations to just distinct values. Redundant duplicates are removed, and only unique values are used.

The CHECKSUM_AGG() function accepts a DISTINCT keyword in its syntax. This allows us to get the checksum based on distinct values:

SELECT CHECKSUM_AGG( DISTINCT Score )
FROM Dogs;

Result:

87

So we can see that the output is different to the output in the previous example, even though we haven’t changed any data. All we did was use the DISTINCT keyword so that the CHECKSUM_AGG() function performs its calculation using only distinct values.

The reason we got a different result is because we do actually have duplicate values in our column. The first two rows have 43 in the Score column, and so they’re duplicates. Using the DISTINCT option means that these two values are treated as one.

The ALL Keyword

We can use the ALL keyword to explicitly state that all values are included – including duplicates:

SELECT CHECKSUM_AGG( ALL Score )
FROM Dogs;

Result:

124

Our result reflects that duplicates were included.

ALL is the default setting when using the CHECKSUM_AGG() function, so we get the same result that we got when we omitted the ALL keyword.

Non-Int Expressions

The expression that CHECKSUM_AGG() operates on must be an int expression. If it’s not an int, then an error occurs.

To demonstrate this, here’s an example of applying CHECKSUM_AGG() to a non-int value:

SELECT 
  CHECKSUM_AGG( Score )
FROM Cats;

Result:

Msg 8117, Level 16, State 1, Server 84489bd0709b, Line 14
Operand data type smallint is invalid for checksum_agg operator.

In this case the column that I passed is of type smallint. So even other integer types such as smallint, tinyint, and bigint are not accepted. It must be int.

We can get around this by converting the expression to an int:

SELECT
    CHECKSUM_AGG( CAST(Score AS int) )
FROM Cats;

Result:

113

This time the code ran without error.