Should You Use the SQL Server Change Tracking Feature?

Should You Use the SQL Server Change Tracking Feature?

What Is SQL Server Change Tracking?

Change tracking was introduced in SQL Server 2008 to help you synchronize data.

It provides relatively easy access to new, modified, and deleted data, removing the need for brute-force comparisons or other costly change-detection tools.

Should We Use Change Tracking?

Change tracking can save you time compared to writing custom code, but it’s not a great fit for databases with very high transaction commit rates on the tracked tables.
This feature is commonly used in data warehouse applications where you need to pull incremental data (changes only) from your source.
It can also be used as a foundation for both one-way and two-way synchronization implementations.

Change Tracking is very useful in these kinds of scenarios.

However, it does add overhead and can lead to potential performance issues.

If you need to use it, make sure you’re only tracking changes on the tables that actually require it (tracking too many tables bloats the internal tables, which can affect performance).

Disable it if you don’t need it.

How do you check whether Change Tracking is enabled?

Use the following script to list every database that has Change Tracking enabled:

SELECT d.name AS 'Database Name', t.*

FROM sys.change_tracking_databases t

INNER JOIN sys.databases d ON d.database_id = t.database_id

How Do I Disable Change Tracking on a SQL Server Database?

You can disable it using the ALTER DATABASE command with the change_tracking option, as shown below:

1. Replace [SampleDatabase] with your database name.

ALTER DATABASE SampleDatabase

SET CHANGE_TRACKING = OFF

You can also use SQL Server Management Studio:

1. Right-click the database in Object Explorer.
2. Click “Properties” and select false on the “Change Tracking” tab: