- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
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 | +------------------+