CHECKSUM_AGG Function in SQL Server

by

In this SQL Server tutorial, You will explain the CHECKSUM_AGG function in SQL Server. With this function, you can perform data integrity checks.

First, I will explain the ‘What is CHECKSUM_AGG() function?’ and its syntax, which describes how to use it.

Afterwards, you will see how to check the data changes in a table using the CHECKSUM_AGG() function.

CHECKSUM_AGG Function in SQL Server

The CHECKSUM_AGG function in SQL Server takes the set of values and returns the checksum value, which indicates the changes in the set of values. This function is an aggregate function which works on a set of values.

This function checks the changes in the group of values (table) and the value it returns as a checksum called the aggregated state of a group of values. Here, a group of values can be those in the table’s column.

In simpler words, The CHECKSUM_AGG function in SQL Server takes all the values of the column of the table and computes the sum, and then if any of the changes are applied to the column values, that checksum value or sum value also changes; this is how the changes are detected.

So, you can do integrity checks and data auditing using the CHECKSUM_AGG() function in SQL Server.

The syntax is given below.

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

Where,

  • CHECKSUM_AGG(): The function computes the check of the group of values.
  • ALL: It is the option that computes the checksum on all the values in the group.
  • DISTINCT: It is the option that computes the checksum on unique values in the group.
  • expression: It is the expression and can be the column, a set of values for which checksum value is returned, and it should be convertible to binary data type.

The return type of the CHECKSUM_AGG() is int, which means it returns the checksum value of the type integer.

Let me show you an example. Here, suppose there is a table for Sales, which is shown below.

CHECKSUM_AGG Function in SQL Server Sales Table

You want to detect changes on this table, especially in the SalesPrice column, so whenever the sales price value changes for any product, you need to know that changes occur to this column.

For that, you first need to compute the checksum value, so use the query below.

SELECT CHECKSUM_AGG(CAST(SalesPrice AS INTEGER)) AS CheckSumValue
FROM Sales;
Using CHECKSUM_AGG Function in SQL Server

As you can see, the checksum is returned over the values of the SalesPrice column, and the checksum value is 9309.

This checksum value is before any modification to the table SalesPrice column. And remember this value because next, we will change the value of the SalesPrice column and then compute the checksum again to detect the changes.

So, update the sale price of any product. For example, change the sale price of the product ‘Wireless mouse’ to 25.11$.

UPDATE SALES
SET SalesPrice = 25.11
WHERE SaleID = 5;

SELECT * FROM Sales;
CHECKSUM_AGG Function in SQL Server Updating Column Value

The output shows that the product ‘Wireless Mouse’ sale price is updated to 25.1100. So here, changes are made to the table. Now, use the query below to compute the checksum value of the SalesPrice column.

SELECT CHECKSUM_AGG(CAST(SalesPrice AS INTEGER)) AS CheckSumValue
FROM Sales;
CHECKSUM_AGG Function in SQL Server

From the output of the above query, the checksum value is 9303, which is different from the previous checksum value, 9309, which indicates a change in the Sales table or the SalesPrice column.

This is how you can use the CHECKSUM_AGG function in SQL Server to detect the changes that occur to the table.

You can use the CHECKSUM_AGG function in SQL Server with options such as ALL and DISTINCT. Whenever you need to compute the checksum value of columns containing duplicate values, use the option ALL within the CHECKSUM_AGG() function, as shown below.

SELECT CHECKSUM_AGG(ALL column_name) FROM table_name;
  • Remember, option ALL is the default. Whether you specify option ALL or not by default, option ALL is used internally while computing the checksum.

If you don’t want to include the duplicate values, use the DISTINCT option within the function, as shown below.

SELECT CHECKSUM_AGG(DISTINCT column_name) FROM table_name;

Also, if your column contains null values, then CHECKSUM_AGG() ignores them. In addition, you are computing the checksum value based on the order of rows, which can’t affect the value.

Conclusion

In this SQL Server tutorial, you learned how to use the CHECKSUM_AGG function in SQL Server to compute the checksum value, which is the aggregated state of the column’s values.

You have used the CHECKSUM_AGG function in SQL Server on the Sales table to detect its changes.

You may like to read: