RSS Feed

CHECKSUM_AGG

CHECKSUM_AGG with Example

CHECKSUM_AGG function shows checksum of values in a group.

CHECKSUM_AGG ignores null values in computation.

CHECKSUM_AGG returns one value for either the entire table, or a specific column to evaluate whether changes have happened. The data type for the CHECKSUM_AGG function must either be a BINARY_CHECKSUM function result or an integer data type.

Example 1 of CHECKSUM_AGG

Calculating CHECKSUM_AGG for the entire table:

CREATE TABLE EMP(x INT PRIMARY KEY,
Name Varchar(20) )

INSERT INTO EMP VALUES (1, 'AAA')
INSERT INTO EMP VALUES (2, 'BBB')
INSERT INTO EMP VALUES (3, 'CCC')
INSERT INTO EMP VALUES (4, 'DDD')
INSERT INTO EMP VALUES (5, 'EEE')
INSERT INTO EMP VALUES (6, 'FFF')

Select * from EMP
X     Name
1      AAA
2      BBB
3      CCC
4      DDD
5      EEE
6      FFF
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM EMP

1799

Example 2 of CHECKSUM_AGG

SELECT CHECKSUM_AGG(Name) FROM EMP

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

As per the definition, it works for only integers.

Example 3 of CHECKSUM_AGG

SELECT CHECKSUM_AGG(x) FROM EMP

7