How To Use CHECKSUM_AGG Function In SQL Server

Checksum returns a value to verify the integrity of input. Generally indicating whether the input value has changed over time. So that this can help us to find if a value has been changed since the last operation. Moreover, SQL Server includes different CHECKSUM functions to verify the change in a single value or group of values.

CHECKSUM_AGG function returns a checksum value for INTEGER type column. It aggregates all the column values and computes a checksum. In case a row(s) changes over time, checksum changes accordingly. This suggests the value has changed in a column. If the checksum value is changed since the last check.

For example, the Employee table has ten different phone numbers in the Contact column. We computed the CHECKSUM_AGG and checksum return 230. If somebody updated his phone number and later applying CHECKSUM_AGG returns 350. Indicating any of the value must have been changed in Contact column.

Points To Ponder – CHECKSUM_AGG

1. Detect changes in the list of values/ table by computing a checksum. Returns INTEGER value as output.

2. This function only works with INTEGER type columns.

3. It ignores NULL thus not considered while computing checksum.

4. This function can be used with the OVER clause.

5. There is a small probability that checksum value might not change as per SQL Server documentation. Make sure you utilize this function in a Production environment after a thorough test.

Examples

Let’s explore CHECKSUM_AGG function with the following examples.

1.

CHECKSUM_AGG – Simple Checksum Computation

In this example using @Players table variable to store players’ salary. Applying CHECKSUM_AGG function to Salary column. It returns a CHECKSUM value as 55272. This may be different in your case. Later running update statement to amend one of the players’ salaries. Rerunning CHECKSUM_AGG function returns a different checksum value 7282. This indicates one of the rows must have changed since the last check. Note that CHECKSUM_AGG will not show which row has changed.

DECLARE @Players TABLE
(
    Salary        INT
   ,Name        VARCHAR(50)
);

INSERT INTO @Players
SELECT *
FROM (VALUES (52222, 'Roger'), (2333, 'Rafa'), (1113, 'Novak'), (4434, 'Jo')) Player (Salary, Name);

SELECT CHECKSUM_AGG(Salary) as OriginalCheckSum  --Checksum Before Update
FROM   @Players;

UPDATE @Players
SET    Salary = 100
WHERE  Name = 'Roger';

SELECT CHECKSUM_AGG(Salary) as AfterCheckSum --Checksum Changes
FROM   @Players;

OriginalCheckSum
----------------
55272

(1 row affected)

AfterCheckSum
-------------
7282

(1 row affected)

2.

CHECKSUM_AGG – Distinct Values CHECKSUM

Applying CHECKSUM_AGG function on distinct integer values returns different checksum. In this example, function returns different checksum for all values and distinct values. Indicating If any of the distinct value has changed since the last check.

USE tempdb;
GO

DECLARE @Players TABLE
(
    Salary        INT
   ,Name        VARCHAR(50)
);

INSERT INTO @Players
SELECT *
FROM (VALUES (2333, 'Roger'), (2333, 'Rafa'), (1113, 'Novak'), (4434, 'Jo'), (1133, 'Andy')) Player (Salary, Name);

SELECT CHECKSUM_AGG(Salary) as OriginalCheckSum  --Checksum Before Update
FROM   @Players;

SELECT CHECKSUM_AGG(Distinct Salary) as OriginalCheckSum  --Checksum Before Update
FROM   @Players;

OriginalCheckSum
----------------
4454

(1 row affected)

OriginalCheckSum
----------------
6267

(1 row affected)

3.

CHECKSUM_AGG – Works Only On Integer Data Type

CHECKSUM_AGG only accepts Integer type column. Running this function on other data types yields in error as following.

DECLARE @Test TABLE
(
    Name VARCHAR(50)
);

INSERT INTO @Test
SELECT 'Test' UNION SELECT 'Test2';

SELECT CHECKSUM_AGG(Name) as CheckSumAgg
FROM   @Test;

Msg 8117, Level 16, State 1, Line 35
Operand data type varchar is invalid for checksum_agg operator.

Summary

As a result CHECKSUM_AGG funtion returns a checksum for list of integer values in a column. This help us to detect changes in a column or a table. SQL Server oferers different checksum functions to verify the integrity of values. If you like this post you may wish to go through Tech-Recipes Database archieve posts for further reading.