What is Change Tracking In SQL Server?
“Without a systematic way to start and keep data clean, bad data will happen.” - Donato Diorio
Change Tracking can be used to track the DML changes (Insert, Update, Delete) performed in SQL Server Database’s and Tables.
SQL Server Change Tracking(CT) is a synchronous tracking method, in which the changed details will be available directly once the DML change is done. This means that SQL Change Tracking does not require the SQL Server Agent service to be up and running. As it has no dependencies on the SQL Agent jobs in capturing or writing the DML changes.
It’s crazy right, Now we can get the data which has been changed in the tables of SQL Servers. Tracking changes can help us identify what changes has been made, thereby ensuring better accuracy in data we preserve.
Now let’s jump directly into the implementation part.
How to enable CT on Database?
1. In order to enable CT on a database table, you should enable it at the database level using the T-SQL statement -
USE Databasename
ALTER DATABASE [dbname]
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
Also, you can enable it manually by
Go to specific database -> Properties -> Change Tracking -> Enable
Here,
Retention Period = Specifies the minimum period for keeping change track information in the database.
Auto Cleanup = If Auto cleanup value is set to false, the log will remain unless and untill we delete it manually.
2. After enabling CT at the database level, we need to enable it on each table so that it will track and audit all the DML changes on it.
This can be achieved using the ALTER TABLE T-SQL statement below:
USE Databasename
ALTER TABLE table_name
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
Also, you can also enable CT using SSMS, from the SQL Change Tracking tab of the Table Properties window, as shown below ->
Now Auditing DML Changes
We have enabled the CT on Database’s. Now all DML changes that are performed on the table rows will be written to the CT internal tables. Assume that we perform some INSERT operations into the employeedetails table:
Now, After INSERTING, we can check the INSERT change by CT Using following statement.
SELECT * FROM CHANGETABLE (CHANGES [table_name],0) as CT ORDER BY SYS_CHANGE_VERSION
Output of the query will be as follows:
Here we can see that 3 records are INSERTED, with deptid 3,4,5.
And the change operation INSERT performed is indicated by ‘I’, Update will be indicated by ‘U’ and Delete by ‘D’.
And if we want to see the records in detail
SELECT * FROM CHANGETABLE (CHANGES [dbo].table_name,0) as CT JOIN [dbo].table_name EM ON CT.primary_key = EM.primary_key ORDER BY SYS_CHANGE_VERSION;
Detailed Output will be as follows-
Here, The data with empid 5 has been INSERTED and it’s indicated by “I” in (SYS_CHANGE_OPERATION).
Also, the data with empid 4 & 1 has been UPDATED and it is indicated by “U”.
What if we want to get records between specific time?
If we want to get records updated for specific time (For eg- Get records updated in last 24 hours).
To get the records according to the time, we need to include timestamp in the data tables. After adding the timestamps we can run the following query.
SELECT * FROM CHANGETABLE (CHANGES [dbo].[table_name],0) as CT JOIN [dbo].[table_name] EM ON CT.Id = EM.Id where ErrorTimeStamp >= DATEADD(day, -1, GETDATE()) ORDER BY SYS_CHANGE_VERSION ;
The records updated in last 24 hours are-
Above are the records updated in last 24 hours.
I Hope till now, you might have got detailed idea about how CT is implemented on SQL Databases and Tables.
Limitations Of CT -
1. Tables that have no Primary Key constraints defined, cannot be audited using Change Tracking.
2. Change Tracking will record no history about the changes performed on a database table, where it will record the last change performed on that row.
(For eg- if a row is inserted, then updated multiple times and finally deleted, CT will only record the last delete statement, without considering the previous operations performed on that row.)
3. You cannot get the exact data that has been changed, you can get only id’s of them or operations performed on them but not the exact data.
Other ways to audit the SQL Changes
1.Change Data Capture (CDC)- CDC also offers the detailed information about the records that has been changed but lacks a user interface to make viewing and processing information easy for users. CDC is asynchronous process, So it can not perform real time output.
2. Temporal tables — This is another feature SQL Server has introduced that offers a complimentary solution to CT. With temporal tables you can see a full history of changes and it can also be used for recovery purposes. See Concept and basics of Temporal tables in SQL Server 2016 for more information on this feature
Summary
If you got to the end of this article, congratulations! You now have the required knowledge to perform CT at your workplace.
If you have any queries, feel free to ask in the comment section down below.
