SQL - CHECKSUM_AGG() Function



Checksums return a value that indicates whether or not the input value has changed over time. The checksum helps us in identifying whether a value has changed since the last operation. Moreover, SQL includes a function called CHECKSUM AGG that can be used to verify a single or group of values.

The SQL CHECKSUM_AGG() function returns the checksum value of the column specified by the given expression. It sums up all of the column values and computes a checksum. If a row or rows change over time, the checksum will change accordingly, indicating that the value in a column has changed. The CHECKSUM_AGG() function ignores null values. We can use the OVER clause along with this function. This function is used only for the numeric columns.

This function also allows the use of two optional modifiers ALL and DISTINCT. If we use ALL, this function calculates the aggregate of all the values. If we use DISTINCT this function returns the number of unique and non null values.

Syntax

Following is the syntax of the SQL CHECKSUM_AGG() function −

CHECKSUM_AGG ( [ ALL | DISTINCT ] expression )

Parameters

  • expression − an integer expression. checksum_agg() does not allow the use of aggregate functions or subqueries.

Example

In the following example, we are using the SQL CHECKSUM_AGG() function to detect changes in the AGE column of the customers table.

Using the CREATE statement, we created a table named customers −

CREATE TABLE customers(ID INT NOT NULL, 
NAME VARCHAR(30) NOT NULL, 
AGE INT NOT NULL, 
ADDRESS CHAR(30), 
SALARY DECIMAL(18, 2));

The table stores the ID, NAME, AGE, ADDRESS, and SALARY. Now we are inserting the 7 records in the customers table using the INSERT statement.

INSERT INTO customers VALUES(1, 'Ramesh', 32, 'Ahmedabad', 2000.00);
INSERT INTO customers VALUES(2, 'Khilan', 25, 'Delhi', 1500.00);
INSERT INTO customers VALUES(3, 'kaushik', 23, 'Kota', 2000.00);
INSERT INTO customers VALUES(4, 'Chaitali', 25, 'Mumbai', 6500.00);
INSERT INTO customers VALUES(5, 'Hardik', 27, 'Bhopal', 8500.00);
INSERT INTO customers VALUES(6, 'Komal', 22, 'MP', 4500.00);
INSERT INTO customers VALUES(7, 'Aman', 23, 'Ranchi', null);

Following SQL query displays the customers table −

SELECT * FROM customers;
Following is the customers table −
+----+----------+-----+-----------+---------+
| ID | NAME     | AGE | ADDRESS   | SALARY  |
+----+----------+-----+-----------+---------+
|  1 | Ramesh   |  32 | Ahmedabad | 2000.00 |
|  2 | Khilan   |  25 | Delhi     | 1500.00 |
|  3 | kaushik  |  23 | Kota      | 2000.00 |
|  4 | Chaitali |  25 | Mumbai    | 6500.00 |
|  5 | Hardik   |  27 | Bhopal    | 8500.00 |
|  6 | Komal    |  22 | MP        | 4500.00 |
|  7 | Aman     |  23 | Ranchi    |    NULL |
+----+----------+-----+-----------+---------+

The following SQL query displays the original checksum value and the updated checksum value after the age column has been updated. −

-- get the checksum value before the column value changed.
SELECT CHECKSUM_AGG(AGE) AS original_checksum from customers;
UPDATE customers SET AGE = 30 WHERE NAME = 'Aman';
--get the checksum of the modified column.
SELECT CHECKSUM_AGG(AGE) AS original_checksum from customers;

Output

Following is the output of the above SQL query −

+--------------------+
|  original_checksum |
+--------------------+
|                 32 | 
+--------------------+

+--------------------+
|  original_checksum |
+--------------------+
|                 36 | 
+--------------------+

Example

In the following example, calling the checksum_agg() function on the distinct integer values, by passing DISTINCT as one of the parameters.

-- get the checksum of all value.
SELECT CHECKSUM_AGG(AGE) AS original_checksum from customers;
--get the checksum of distinct value.
SELECT CHECKSUM_AGG(DISTINCT AGE) AS original_checksum from customers;

Output

Following is the output of the above SQL query −

+--------------------+
|  original_checksum |
+--------------------+
|                 36 | 
+--------------------+

+--------------------+
|  original_checksum |
+--------------------+
|                 61 | 
+--------------------+

Example

In the following example, we are using the SQL CHECKSUM_AGG() function to detect changes in the SALARY column of the customers table. The CHECKSUM_AGG() function always accepts the column of the type integer. Since the salary column contains decimal data type, we must cast it into INT.

The following SQL query, displays the original and modified checksums of SALARY −

--original checksum
SELECT CHECKSUM_AGG(CAST(SALARY AS INT)) AS original_SALARY from customers;
UPDATE customers SET SALARY = 5000 WHERE NAME = 'Aman';
--checksum after modified
SELECT CHECKSUM_AGG(CAST(SALARY AS INT)) AS original_SALARY from customers;

Output

Following is the output of the above SQL query −

+------------------+
|  original_SALARY |
+------------------+
|            11288 | 
+------------------+

+------------------+
|  original_SALARY |
+------------------+
|            16272 | 
+------------------+
sql-aggregate-functions.htm
Advertisements