CHECKSUM_AGG (Transact-SQL)
Returns the checksum of the values in a group. Null values are ignored. Can be followed by the OVER clause.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )
Arguments
- ALL
Applies the aggregate function to all values. ALL is the default.
- DISTINCT
Specifies that CHECKSUM_AGG returns the checksum of unique values.
expression
Is a constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the int data type. Aggregate functions and subqueries are not allowed.For more information, see Expressions (Transact-SQL).
Return Types
Returns the checksum of all expression values as int.
Remarks
CHECKSUM_AGG can be used to detect changes in a table.
The order of the rows in the table does not affect the result of CHECKSUM_AGG. Also, CHECKSUM_AGG functions may be used with the DISTINCT keyword and the GROUP BY clause.
If one of the values in the expression list changes, the checksum of the list also generally changes. However, there is a small chance that the checksum will not change.
CHECKSUM_AGG has similar functionality with other aggregate functions. For more information, see Aggregate Functions (Transact-SQL).
Examples
The following example uses CHECKSUM_AGG to detect changes in the Quantity column of the ProductInventory table in the AdventureWorks database.
--Get the checksum value before the column value is changed.
USE AdventureWorks;
GO
SELECT CHECKSUM_AGG(CAST(Quantity AS int))
FROM Production.ProductInventory;
GO
Here is the result set.
------------------------
262
UPDATE Production.ProductInventory
SET Quantity=125
WHERE Quantity=100;
GO
--Get the checksum of the modified column.
SELECT CHECKSUM_AGG(CAST(Quantity AS int))
FROM Production.ProductInventory;
Here is the result set.
------------------------
287
See Also
Reference
CHECKSUM (Transact-SQL)
OVER Clause (Transact-SQL)