T-SQL Checksum Command Example on SQL Server
T-SQL CHECKSUM command is used to return the checksum value of a SQL Server database table row, or to return the checksum value of an expression.
Checksum can be used by sql developers or SQL Server database administrators to determine if column values of a table row has been changed or not.
Here is the CheckSum syntax from MSDN :
CHECKSUM ( * | expression [ ,...n ] )
And below you can find sample select statements for checksum command examples demonstrating basic usages of T-SQL CheckSum command.
One example is used for calculation checksum value for an expression.
The second sample is used for computing checksum integer value of compound columns of a table row in a SQL Server database.
The last Checksum example is demonstrating caluculating checksum of an entire table row considering all column values in the related database table.
In order to checksum for all row you can use the "*" for indicating "all columns".
SELECT CHECKSUM(N'This is a T-SQL CHECKSUM sample code')
SELECT CHECKSUM(Title, BirthDate, ManagerID)
FROM HumanResources.Employee
WHERE EmployeeID = 1
SELECT CHECKSUM(*) FROM HumanResources.Employee WHERE EmployeeID = 2
And the output for the above CheckSum sample select statements are integer values.
Now developers can extend the use of CheckSum for preventing row updates if row has been changed or data for the selected record has been altered during the time passed for the developers' update decision.
In order to prevent update of a changed row, you can compare the previous values of each column in the related table row with the existing values using a IF EXISTS() statement and selecting from the related table with criterias filtered using the previous column values.
If there still exists the same row in the table, then you can update table for the related row.
But this process is difficult since you have to code a huge WHERE clause including every column of the table.
Instead of that, sql developers can use the CheckSum command to indicate if data has been changed or altered.
Here is a sample case where we will code using checksum statement. First we will create a database table and fill it with sample data.
CREATE TABLE Employee (
Id int identity(1,1),
FirstName nvarchar(25),
LastName nvarchar(25),
HRNo nvarchar(25),
[CheckSum] as CHECKSUM(Id, FirstName, LastName, HRNo)
)
GO
insert into Employee select N'Anakin', N'Skywalker','AS001'
insert into Employee select N'Padme', N'Amidala','PA001'
insert into Employee select N'Luke', N'Skywalker','AS002'
insert into Employee select N'Han', N'Solo','HS001'
insert into Employee select N'Luke', N'Skywalker','AS002'
insert into Employee select N'Padme', N'Amidala','PA001'
insert into Employee select N'Leia', N'Solo','LS001'
What is important about the sample table is we are creating a sql computed column within the databae table which keeps the checksum value of the four columns we consider.
Let's update the table row with Id value equals to 7.
SELECT * FROM Employee WHERE Id = 7
DECLARE @Id int
SET @Id = 7
DECLARE @FirstName nvarchar(25), @LastName nvarchar(25), @HRNo nvarchar(25)
DECLARE @FirstName_Old nvarchar(25), @LastName_Old nvarchar(25), @HRNo_Old nvarchar(25)
SELECT
@FirstName = N'Leia', @LastName = N'Solo', @HRNo = N'HS001',
@FirstName_Old = N'Leia', @LastName_Old = N'Solo', @HRNo_Old = N'LS001'
UPDATE Employee
SET
FirstName = @FirstName,
LastName = @LastName,
HRNo = @HRNo
WHERE
Id = @Id AND
[CheckSum] = CHECKSUM(@Id, @FirstName_Old, @LastName_Old, @HRNo_Old)
SELECT * FROM Employee WHERE Id = 7
The above code will check the stored checksum value in the computed column named [checksum] for the checksum of previous values at hand.
And if the integer values match, then it will update the table row. Else no row will be updated.
By checking the @@RowCount value sql developers can easily indicate whether any row is updated or not.
|