SQL Server Change Tracking

Todd Henson   •   07.15.2019

A Lightweight and Reliable Method for
Managing Incremental Loads to your Data Warehouse

Available in all versions of SQL Server starting with 2008R2, SQL Server Change Tracking (CT) is an excellent option for tracking changes in your source SQL Server tables. CT is easy to implement, has a low impact on server performance, and is highly reliable.

What is so great about incremental loads? Why not just reload all the rows each time?

Incremental loads to your data warehouse are a necessity for two main reasons:

  1. If you have huge tables in your SQL Server environment, ETL performance improves by processing only those rows that have changed.
  2. Allows a level of historical tracking for slowly changing dimensions, etc

Why not use a date column or hashing to identify changes?

Using a modified date is unreliable when either the application or a DBA updates the data without updating the modified date. We’d like to say that would never happen, but we all know it does. Hashing provides a maintenance headache when adding columns from the source table or removing, and we must remember to update the HASH to include/remove those columns.

So how is SQL Server Change Tracking implemented?

You must first use the ALTER DATABASE command to enable CT on the source database:

USE Master

GO

ALTER DATABASE CT_DEMO SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 Days, AUTO_CLEANUP = ON)

Then use ALTER TABLE command to enable CT on each source table:

USE CT_DEMO

GO

ALTER TABLE dbo.SourceTable1 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

ALTER TABLE dbo.SourceTable2 ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = OFF)

Enabling CT on a table creates another hidden internal table that stores:

  1. the primary key of each modified row (NOTE: CT only works on tables with a primary key)
  2. the operation performed (I)nsert, (U)pdate, (D)elete
  3. the version number for the change

That sounds easy enough, but how do you access the changes from those built-in tables?

Microsoft provides built-in SQL functions CHANGETABLE and CHANGE_TRACKING_CURRENT_VERSION to help us access the internal CT tables and identify the rows that have changed. These functions are used in T-SQL SELECT queries to extract the data from your source tables.

Simple t-SQL MERGE query can be used to load the changes from the landing table into the destination table. MERGE allows you to handle Inserts, Updates, and Deletes against the destination table in whatever manner you require.

You’re convinced but would still like help to implement SQL Server Change Tracking in your data warehouse environment?

We’d be more than happy to set up a meeting or have a conversation to discuss your needs and explain how we might be able to help. Please send an email to sales@talavant.com and we’ll get in touch.


Todd Henson

Senior Consultant

Share This Article


At Talavant, our goal is to help you discover, ask and answer the right questions – so you get more value from your data to drive your business forward. You’ll gain a competitive edge with faster, more relevant analytics. It’s all part of our focused, holistic approach that goes beyond tools.

Ready to get started?

Contact Us