Get instant live expert help with SQL
“My expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Enter your problem description Our Experts are available now

Your message must be at least 40 characters
Select your SQL Environment:
Your message must be at least 40 characters
By submitting this form you agree to Got It's privacy policy.

How the Change Tracking Feature Works in SQL Server – Querychat

In this article, we learn how the change tracking feature works in SQL Server for a database table changes. Providing examples of tracking DML changes to a table specified with the SQL Server CHANGETABLE function.

SQL Server provides tracking mechanism features in all editions since version 2008. It tracks Data Manipulation Language (DML) statements committed in the specified table. To find out more about how this feature works, let’s move on to the sections below.

SQL Server change tracking settings

Enabling change tracking has two processes that we must set first, at the database and table level. That can all be done via the SSMS UI Change Tracking tab in each of its properties. 

Database level

The change tracking configuration at the database level can be done using the ALTER DATABASE statement with the value set to ON to. When the value is set to OFF it disables the change tracking

USE master

GO

ALTER DATABASE [Database_name]

SET CHANGE_TRACKING = [ ON | OFF ] 

(

   AUTO_CLEANUP = [ ON | OFF ],

   CHANGE_RETENTION = [N] DAYS

)

There are two options above, the AUTO_CLEANUP and CHANGE_RETENTION, to enable the automatic cleaning process in the specified table according to the specified retention period. Enabling the change tracking at the database level can also be done through the database properties as shown below: 

Figure 1. Enabling the change tracking in the database level

Table level

Once the database-level change tracking is configured, we need to enable the change tracking at the table level. The ALTER TABLE statement is used to enable or disable it. Optionally, we can track the updated column in the specified table. Let’s see the following syntax:

USE [Database_name]

GO

ALTER TABLE [Table_name] [ ENABLE | DISABLE ] CHANGE_TRACKING

WITH ( TRACK_COLUMNS_UPDATED = [ ON | OFF ] )

We can also enable the change tracking in table properties as shown below: 

Figure 2. Enabling the change tracking in the table level

Enabling the change tracking at the table-level that doesn’t have a primary key on the specified table will trigger an error message like the following:

Figure 3. An error message of the change tracking a table without a primary key

SQL Server CHANGETABLE function

The CHANGETABLE function returns tracking information for all changes in the specified table. It contains the version of the DML changes made, the modified columns, and the types of DML operations such as I (INSERT), U (UPDATE), or D (DELETE). Here’s the syntax to run the function:

CHANGETABLE (

   CHANGES [Table_name] , [Change_tracking_version]

) AS [Alias_name]

Our example playground

Here is a database named myDB and a table named myTB for our demonstration purpose. The table consists of an ID and name columns and contains a few sample records. The following syntax is how we create them through the Transact-SQL:

CREATE DATABASE myDB;

GO

USE myDB

GO

CREATE TABLE myTB (id INT PRIMARY KEY, name VARCHAR(50))

Once the database and table are created, we enable the change tracking feature on the myTB table and myDB database with 1-day change retention and automatic cleaning as follows:

USE master

GO

 

ALTER DATABASE myDB

SET CHANGE_TRACKING = ON

(AUTO_CLEANUP = ON, CHANGE_RETENTION = 1 DAYS)

GO

 

USE myDB

GO

ALTER TABLE myTB

ENABLE CHANGE_TRACKING

WITH (TRACK_COLUMNS_UPDATED = ON)

Example #1 Using the INSERT statement

We insert a few records into the  

INSERT INTO myTB VALUES

(1, 'Arifin'), (2, 'Fistammy'), (3, 'Zepsy')

To track new data entered above, we run the SELECT statement to the myTB table that joins the CHANGETABLE function with version 0 as follows:

SELECT B.id, A.name, B.* FROM myTB AS A

JOIN CHANGETABLE (CHANGES myTB, 0) AS B ON A.id=b.id

The result will appear:

id name SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT
1 Arifin 1 1 l NULL NULL
2 Fistammy 1 1 l NULL NULL
3 Zepsy 1 1 l NULL NULL

Table 1. Result of the INSERT changes 

Example #3 Using the UPDATE statement

We modify a name, Zepsy, in the myTB table with a new name, Zepsy Ningrum. See the following syntax using the UPDATE statement:

UPDATE myTB

SET name='Zepsy Ningrum'

WHERE id=3

We execute the SELECT statement to the myTB table joining with the version of 1 in the CHANGETABLE function to track the data changes as follows:

SELECT B.id, A.name, B.* FROM myTB AS A

JOIN CHANGETABLE (CHANGES myTB, 1) AS B ON A.id=b.id

The result will appear:

id name SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT
3 Zepsy Ningrum 2 NULL U NULL NULL

Table 2. Result of the UPDATE changes 

Example #4 Using the DELETE statement

We delete a name, Arifin, with an id of 1, Zepsy, in the myTB table. The syntax is as follows:

DELETE FROM myTB WHERE id=1

Tracking the deleted row above, we run the SELECT statement to the myTB table left-joining the CHANGETABLE function with the version of 2 as follows:

SELECT B.id, A.name, B.*

FROM CHANGETABLE (CHANGES myTB, 2) AS B

LEFT JOIN myTB AS A ON A.id=b.id

The result will appear:

id name SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT
1 NULL 3 NULL D NULL NULL

Table 3. Result of the DELETE changes

Instant livechat to an Expert!

Most of the time, the problem you will need to solve will be more complex than a simple method. If you want to save hours of research and frustration, try our live SQLExpert service! 

Our SQLQuerychat Experts are available 24/7 to answer any questions you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Leave a Reply

avatar
  Subscribe  
Notify of
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc