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.
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
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:
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:
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:
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.