Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
Creates a checksum value of a data set. You can use it to check if data in a column is being modified. accepts int data type as a parameter and returns int value as a result.
Let’s make examples for a better understanding of CHECKSUM_AGG Function.
Example:
First, we create a table with the help of the following script and add a few records into this table.
1 2 3 4 5 6 7 | CREATE TABLE [dbo].[MyTable] ([ID] INT IDENTITY, [Name] VARCHAR(20), [Age] INT, [BirthDay] VARCHAR(8)) INSERT [dbo].[MyTable] VALUES ('Hakan GURBASLAR',36,'19830101'),('Nurullah CAKIR',34,'19850505'),('Faruk ERDEM',27,'19910101') |
We calculate the checksum of the data in the Age column with the CHECKSUM_AGG function as follows.
1 | Select CHECKSUM_AGG([Age]) FROM [dbo].[MyTable] |
Perform an update in the Age column as follows and recalculate the checksum value.
1 2 3 | UPDATE [dbo].[MyTable] SET [Age]=40 WHERE [Name]='Nurullah CAKIR' GO Select CHECKSUM_AGG([Age]) FROM [dbo].[MyTable] |
As you can see, the checksum value has changed.
Update the data to its old value and calculate the checksum again.
1 2 3 | UPDATE [dbo].[MyTable] SET [Age]=34 WHERE [Name]='Nurullah CAKIR' GO Select CHECKSUM_AGG([Age]) FROM [dbo].[MyTable] |
As you can see, its the first calculated checksum value.
When we run the checksum function in a column of the string(varchar,nvarchar etc) data type, we receive the error as follows.
1 | Select CHECKSUM_AGG([BirthDay]) FROM [dbo].[MyTable] |
Msg 8117, Level 16, State 1, Line 13
Operand data type varchar is invalid for checksum_agg operator.
We can use this function without error for string data type as follows.
1 | Select CHECKSUM_AGG(CAST([BirthDay] AS INT)) FROM [dbo].[MyTable] |
Of course, we cannot convert each data to the INT data type with CAST. When we try to perform the same operation for the [Name] column, we get the following error.
1 | Select CHECKSUM_AGG(CAST([NAME] AS INT)) FROM [dbo].[MyTable] |
Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value ” to data type int.
