How To Compare Table Data And Detect Changes In SQL Server

Recently, I was working on a problem scenario where i need to maintain the histories of the table row data if any updates occurs to a row in the table. And histories maintenance should be maintained only if any data change is there while updating a table row.

This post is demonstrate how the same can be achieved in SQL Server.
Let’s say that we have a sample Student Table as :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Create Student Table
 
CREATE TABLE STUDENT
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	StdCode INT,
	FirstName nvarchar(30),
	MiddleName nvarchar(30),
	LastName nvarchar(30),
	Gender nvarchar(30),
	Class nvarchar(30),
	Address nvarchar(30),
	STATUS BIT DEFAULT(1)
)
 
-- Insert Sample Data into Student Table
 
INSERT INTO STUDENT (StdCode,FirstName,MiddleName,LastName,Gender,Class,Address,STATUS)
	VALUES (1,'James','M','Alexander','Male','6th','Spring Hill, FL, United States',1)
Student-Table

Now, Let’s say we need to update the Student ‘James’ class information from ‘6th’ to ‘7th’ and other columns values should be as it is, maintaining previous data as historical information.

This needs first comparing the current row information with the data being updated.

Solution – 1 : Compare Tables Row Data Using the EXCEPT Clause

We can compare two result-set to identify the difference using EXCEPT Clause as :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
CREATE PROCEDURE UpdateStudent 
	-- Add the parameters for the stored procedure here
 
	@StdCode INT,
	@FirstName NVARCHAR(30),
	@MiddleName NVARCHAR(30),
	@LastName NVARCHAR(30),
	@Gender NVARCHAR(10),
	@Class NVARCHAR(30),
	@Address NVARCHAR(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Detect if Row data changes
	IF EXISTS (SELECT FirstName,MiddleName,LastName,Gender,
                   Class,Address FROM STUDENT 
                   WHERE StdCode=@StdCode AND STATUS=1
				EXCEPT
		SELECT @FirstName,@MiddleName,
                @LastName,@Gender,@Class,@Address)
	BEGIN
		-- duplicating the exsisting student details
		INSERT INTO STUDENT 
                 (StdCode,FirstName,MiddleName,
                  LastName,Gender,Class,Address,STATUS)
		  SELECT @StdCode,FirstName,MiddleName,
                  LastName,Gender,Class,Address,0 
                  FROM STUDENT WHERE StdCode=@StdCode 
                  AND STATUS=1
 
		--Updating changed student details 
		UPDATE STUDENT SET 
                FirstName=@FirstName,MiddleName=@MiddleName,
 
                LastName=@LastName,Gender=@Gender,Class=@Class,
		Address = @Address 
                WHERE StdCode=@StdCode AND STATUS=1
	END
END
GO

Now, Let’s Try to update the Student ID = 1 by calling the UpdateStudent Store procedure.

1
2
EXEC UpdateStudent @StdCode=1,@FirstName='James',@MiddleName='M',
@LastName='Alexander',@Gender='Male',@Class='7th',@Address='Spring Hill, FL, United States'

You can see in below image, we have two record for the student with ID=1 maintaining previous data in Inactive State.

Student-Table-History

Solution – 2 : Detect changes in a row of a table using in-built function CHECKSUM()

CHECKSUM function computes a hash value, called the checksum, over its argument list. This function can compute checksum value for an entire row or list of expressions or columns. It returns a computed integer value.

Syntax :

1
CHECKSUM ( * | expression [ ,...n ] )

CHECKSUM() function returns an error if any column has a non-comparable data type.
Non-comparable data types are text, ntext, image, XML, and cursor, and also sql_variant.

Let’s see some examples :

1). Compute checksum value for built-int data types.

built-in-types-checksum

2). Compute checksum value for an expression.

expression-checksum

3). Compute checksum value for an entire row.

row-checksum

4). Compute checksum value for set of columns.

specific-row-checksum

5). Detect row-changes using Checksum

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
ALTER PROCEDURE [dbo].[UpdateStudent_Using_Checksum] 
	-- Add the parameters for the stored procedure here
 
	@StdCode INT,
	@FirstName NVARCHAR(30),
	@MiddleName NVARCHAR(30),
	@LastName NVARCHAR(30),
	@Gender NVARCHAR(10),
	@Class NVARCHAR(30),
	@Address NVARCHAR(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @computedChecksum INT;
	SELECT @computedChecksum =  CHECKSUM(FirstName,MiddleName,
                     LastName,Gender,Class,Address) 
                     FROM STUDENT WHERE StdCode=@StdCode 
                     AND STATUS=1
    -- Detect if Row data changes
	IF (@computedChecksum <> CHECKSUM(@FirstName,@MiddleName,
            @LastName,@Gender,@Class,@Address))
	BEGIN
		-- duplicating the exsisting student details
		INSERT INTO STUDENT (StdCode,FirstName,MiddleName,
                 LastName,Gender,Class,Address,STATUS)
		 SELECT @StdCode,FirstName,MiddleName,
                 LastName,Gender,Class,Address,0 
                 FROM STUDENT WHERE StdCode=@StdCode 
                 AND STATUS=1
 
		--Updating changed student details 
		UPDATE STUDENT SET 
                FirstName=@FirstName,MiddleName=@MiddleName,
 
                LastName=@LastName,Gender=@Gender,Class=@Class,
		Address = @Address WHERE StdCode=@StdCode 
                AND STATUS=1
	END
END

OUTPUT :
Now, You can see in below image, we have three record for the student with ID=1 maintaining previous records in Inactive State.

detect-row-changes

From the above example, you could understand how we can detect row changes using EXCEPT and CHECKSUM function

If you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.

Happy Learning 🙂

Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming.  He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.