CHECKSUM_AGG() function is used to compute checksum value for a group of integer values. The return data type of this function is an int. This function ignores NULL values in the group and it can be used with OVER clause also.
We can compute checksum for a group with ALL values or DISTINCT values or an integer expression. ALL and DISTINCT are the keywords that can be place in front of the function argument to get the desire result.
Actually, this function can be used to detect the changes in a table.
Implementation
Compute checksum with all values in a table. Checksum computation before change
Checksum computation with changes. So, it returns different checksum value than the previous output.
ALL is the default argument. So, it computes checksum for the entire table. But, we are going to use DISTINCT keyword to compute checksum now.
It is a useful function to detect the changes in a table. But, It may not detect the small changes and returns same output for different list of values. So, We have to use this function with extra care. For instance, swapping row values may not yield different checksum value.
Pingback: Detect changes in a row of a table using BINARY_CHECKSUM() – SQL SERVER | SQL Server Rider