For an instructor lead, in-depth look at learning SQL click below.
Change tracking in SQL Server is a lightweight solution that provides an efficient change tracking mechanism for applications. Unlike other change tracking mechanisms, change tracking in SQL is simple to use and provides valuable information about the changes without any unneeded details.
Activating Change Tracking
To begin with, you must activate the change tracker on the SQL Server. It should be enabled at the database level by using the SQL statement:
|
1 2 3 4 5 |
ALTER DATABASE YourDatabase SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON) |
The statement above sets the change tracking retention period as two days, and the AUTO_CLEANUP as ON so that auto-cleanup thread can wake up to remove stale changes.
Enabling Change Tracking on a Table
Once you have enabled change tracking at a database level, you must activate it for each table you wish to track. Using the statement below, you can enable change tracking:
|
1 2 3 4 |
ALTER TABLE YourTable ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON) |
Querying Changes
After change tracking is activated, you can start querying the changes made to the records in your table. SQL Server has provided a built-in function called CHANGETABLE, which serves to get change information:
|
1 2 3 |
SELECT * FROM CHANGETABLE(CHANGES YourTable, @last_synchronization_version) AS CT |
The last_synchronization_version parameter is the database version when you last checked for changes. If you use zero as last_synchronization_version, CHANGETABLE will return all changes since tracking was enabled on YourTable.
These are the basics of implementing change tracking on SQL Server. The information supplied covers most scenarios, but you may need to customize these code snippets according to your particular situation.