SQL Server CHECKSUM_AGG() Function

Summary: in this tutorial, you will learn how to use the SQL Server CHECKSUM_AGG() function to detect the data changes in a column.

Introduction to SQL Server CHECKSUM_AGG() function

The CHECKSUM_AGG() function is an aggregate function that returns the checksum of the values in a set.

The following shows the syntax of the CHECKSUM_AGG() function:

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression)  Code language: SQL (Structured Query Language) (sql)

In this syntax:

ALL instructs the function to return the checksum of all values including duplicates.
DISTINCT forces the function to calculate the checksum of distinct values.
expression is an integer expression. The function does not accept subqueries or aggregate functions.

The CHECKSUM_AGG() function ignores null values.

Because of the hashing algorithm, the CHECKSUM_AGG() function may return the same value with different input data. Therefore, you should use this function when your application can tolerate occasionally missing a change.

SQL Server CHECKSUM_AGG() function example

The following statement creates a new table called sales.inventory with the data retrieved from the production.stocks table in the sample database. The new table stores products and their quantities:

SELECT
    product_id, 
    SUM(quantity) quantity
INTO 
    sales.inventory
FROM
    production.stocks
GROUP BY 
    product_id;Code language: SQL (Structured Query Language) (sql)

The following statement uses the CHECKSUM_AGG() function to get the aggregate checksum of the quantity column:

SELECT 
    CHECKSUM_AGG(quantity) qty_checksum_agg
FROM
    sales.inventory;Code language: SQL (Structured Query Language) (sql)

Output:

qty_checksum_agg
----------------
29

(1 row affected)

Let’s change the data in the sales.inventory table:

UPDATE 
    sales.inventory
SET
    quantity = 10
WHERE
    product_id = 1;Code language: SQL (Structured Query Language) (sql)

And apply the CHECKSUM_AGG() function to the quantity column:

SELECT 
    CHECKSUM_AGG(quantity) qty_checksum_agg
FROM
    sales.inventory;Code language: SQL (Structured Query Language) (sql)

Output:

qty_checksum_agg
----------------
32

(1 row affected)

The output indicates the result of the CHECKSUM_AGG() changes. It means that the data in the quantity column has been changed since the last checksum calculation.

Summary

Use the CHECKSUM_AGG() function to detect the data changes in a column.

Was this tutorial helpful?