How to Enable Change Tracking – SQL [Complete Guide 2024]

Businesses perform several tasks like auditing data records, checking malicious activities, tracking security violations, and ensuring seamless business operations. They often use SQL servers to track database activities and audit the changes in their data. With this, all the database operations, like insertion, deletion, and updation, are captured periodically. 

The change tracking feature in SQL is one such feature that enables companies to capture the changing rows and columns in a database. Change tracking in SQL is a simple mechanism that can capture incremental changes in a specified window. This article will introduce you to the change tracking mechanism in SQL servers and how to enable and disable it.

How to Enable Change Tracking for a Database 

SQL Servers provide two features to track changes to data in a database. The first is change data capture, and the second is change tracking. Change data capture is the method of identifying and storing the changes that happen in databases. While change tracking is the process where database changes are identified, it does not determine the data that is changed. Both change data capture and change tracking can enable DML changes and track which rows and columns were changed. But, only change data capture can determine the exact changes in databases.

The ability to query for changes in a database is an essential requirement for some database applications. To determine the data changes, application developers implement custom tracking event methods like triggers, timestamps, and additional columns in applications. But deploying these methods requires custom implementation, which leads to schema updates and causes high-performance overhead. 

As a result, businesses use change tracking or data capture mechanisms for databases to track changes instead of developing a custom solution.

To use change tracking in the SQL server, you must first enable change tracking at the database level. After enabling the change tracking at the database level, you can enable change tracking for each table in the database. 

The example below shows how to allow change tracking using the ALTER DATABASE.

ALTER DATABASE AdventureWorks2012  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)

• CHANGE_TRACKING: It is the parameter used to enable/disable change tracking in the SQL server.
• CHANGE_RETENTION: It is the option used to specify the time for which change tracking information is kept in the database. The default value for CHANGE_RETENTION is 2 days.
• AUTO_CLEANUP: It is the option used to remove all the old change tracking information. 

Businesses can also enable change tracking with SQL Server Management Studio by leveraging the dialog box of the database properties (ChangeTracking page). 

How to Enable Change Tracking for a Table

Change tracking needs to be enabled for every table you want to track. Then, the change tracking information is collected for all rows in the tables which are affected by DML operations.

The example below shows how to enable change tracking for a table using ALTER TABLE.

ALTER TABLE Person.Contact  
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = OFF)

TRACK_COLUMNS_UPDATED: It is the option used by the SQL server database engine to store extra information about the columns. This information helps in improving efficiency and performance. But maintaining column information adds to some extra storage overhead, and hence the TRACK_COLUMNS_UPDATED is set to OFF by default.

You can also enable change tracking for tables in SQL server management studio by leveraging the Database Properties dialog box.

Check Change Tracking Enablement 

1. Enable change tracking at the database level in the SQL server

Check if you’ve enabled change tracking at the database level with the following syntax:

SELECT *
FROM sys.change_tracking_databases
WHERE database_id = DB_ID(‘databasename’);

For example:

SELECT *
FROM sys.change_tracking_databases
WHERE database_id = DB_ID(‘Adventureworks2019’);

Output:

The SQL server will show the above output after enabling the change tracking mechanism on a database level. 

The database with database_id as 8 has been enabled for change tracking. 

2. Enable change tracking at the table level in the SQL server

Check if you’ve enabled change tracking at the table level with the following syntax:

USE databasename;
SELECT sys.schemas.name as schema_name, sys.tables.name as table_name
FROM sys.change_tracking_tables
JOIN sys.tables ON sys.tables.object_id = sys.change_tracking_tables.object_id
JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id;

For example:

USE Adventureworks2019;
SELECT sys.schemas.name as schema_name, sys.tables.name as table_name
FROM sys.change_tracking_tables
JOIN sys.tables ON sys.tables.object_id = sys.change_tracking_tables.object_id
JOIN sys.schemas ON sys.schemas.schema_id = sys.tables.schema_id;

Output:

DEMO_SECOND is the table for which the change tracking has been enabled.

How to Disable Change Tracking for a Database or a Table

When there are no tables in the database to track changes, disable the change tracking for the database. The below example shows how to disable change tracking for a database with the ALTER DATABASE in the SQL server.

ALTER DATABASE AdventureWorks2012  
SET CHANGE_TRACKING = OFF

Here, CHANGE_TRACKING: is the parameter used to enable or disable change tracking in the SQL server.

The below example shows how to disable change tracking for a table via ALTER TABLE in the SQL server.

ALTER TABLE Person.Contact  
DISABLE CHANGE_TRACKING;

Summing Up

Change tracking in SQL servers helps track changes in databases. Since many people handle databases, change tracking is essential for database administrators to track all the modifications. Change tracking is a lightweight solution supported by all versions of SQL servers. You must use the feature to ensure you keep track of the changes in mission-critical applications.

Getting data from many sources into destinations can be a time-consuming and resource-intensive task. Instead of spending months developing and maintaining such data integrations, you can enjoy a smooth ride with Hevo Data’s 150+ plug-and-play integrations (including 40+ free sources).

Visit our Website to Explore Hevo Data

Saving countless hours of manual data cleaning & standardizing, Hevo Data’s pre-load data transformations get it done in minutes via a simple drag n drop interface or your custom python scripts. No need to go to your data warehouse for post-load transformations. You can run complex SQL transformations from the comfort of Hevo’s interface and get your data in the final analysis-ready form. 

Want to take Hevo Data for a ride? Sign Up for a 14-day free trial and simplify your data integration process. Check out the pricing details to understand which plan fulfills all your business needs.