For an instructor lead, in-depth look at learning SQL click below.
Every substantial work of data repository needs a process where changes are monitored and tracked over time. SQL Server’s Change Tracking features provide age-old solutions to this kind of challenge. Today, we will explore this handy feature of SQL Server.
What is SQL Server Change Tracking?
SQL Server Change Tracking (CT) is a lightweight and responsive feature that tracks DML changes (Insert, Update, and Delete actions) on a database table. Unlike other methods like Change Data Capture (CDC) or Temporal Tables, CT does not record the data related to changes but instead, keeps track of the fact that rows have been changed.
Enabling Change Tracking
To use Change Tracking, it has to be enabled at both the Database level and the Table level. To enable CT at the database level, you must use the ALTER DATABASE statement:
|
1 2 3 4 5 |
ALTER DATABASE YourDatabase SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON); |
This statement will enable change tracking on the database ‘YourDatabase’ and keeps the change tracking data for 2 days. ‘AUTO_CLEANUP = ON’ automatically cleans up tracking info older than the retention period.
Once the database level setting is enabled, you can then enable CT at the table level using the ALTER TABLE statement:
|
1 2 3 4 5 |
ALTER TABLE YourTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON); |
Here, the ‘TRACK_COLUMNS_UPDATED = ON’ option will store the information if any column value has been updated in the rows of ‘YourTable’.
Change Tracking Functions
After enabling change tracking, SQL Server provides a set of system functions to retrieve change information.
For Example:
|
1 2 3 4 5 6 7 8 9 10 |
SELECT SYS_CHANGE_VERSION, SYS_CHANGE_CREATION_VERSION, SYS_CHANGE_OPERATION, SYS_CHANGE_COLUMNS, SYS_CHANGE_CONTEXT FROM CHANGETABLE(CHANGES YourTable, 0) AS CT |
This script retrieves all changes that have occurred in ‘YourTable’ since the version 0.
Conclusion
By integrating SQL Server Change Tracking into your system operations, you get a reliable, secure, and efficient way of tracking data modifications. Mastering it is a definite boost to your SQL Server tool repertoire. Keep practising and stay tuned for more!