Change Tracking in Azure SQL Database

Introduction

This article discusses Change Tracking in Azure SQL Database and how it can be used to track changes in tables.

Why use Change Tracking?

If you only need to know about changes over a set time period and don't need historical data, Change Tracking is a good option instead of Change Data Capture.

Enabling Change Tracking

To enable Change Tracking, use the ALTER TABLE statement with the CHANGE_TRACKING clause.

Examining Tracked Tables

We can use the CHANGE_TRACKING_CURRENT_VERSION function to get the current version number, which is the minimum number used to retrieve change tracking information.

Querying the Change Tracking DMV

We can use the sys.dm_tran_commit_table system function to query the Change Tracking DMV and retrieve information about which tables are enabled.

Applying Changes

We can apply changes by extracting the changed rows and transferring them to another database for reporting and analytics.

Playing with Version Numbers

We can manipulate version numbers to track changes and retrieve specific updates.

Clean Up

We can change the retention period for Change Tracking and use the sys.sp_flush_CT_internal_table_on_demand system function to clean up the side tables for all Change Tracking enabled tables.