Tracking Changes in SQL Server Databases — A Brief Guide
Overview — Why is it required to track changes?
Tracking changes helps us know who and what changes were made thereby ensuring better accuracy in data that we preserve.
Change tracking mechanisms can be developed by developers but that would involve sufficient development changes and modifications to schema resulting in significant development efforts. It would also have significant performance overhead. Hence, SQL Server provides different in-built tracking mechanisms like -
- Change Tracking
- Change Data Capture
- System-versioned Temporal Tables.
These built-in tracking mechanisms also have an overhead associated with them, but this is a lot less compared to custom-built tracking mechanisms.
Let’s try to compare and analyze the differences between different tracking mechanisms.
Change Tracking
Change Tracking tracks what DML statements were used to perform the change. An important thing to note here is that the Change Tracking utility does not track changes to historic data. In addition to tracking DML changes done to the tracked table, Change Tracking also tracks the column that was changed. Change Tracking captures only the fact that rows are being changed but does not track the content that was changed. Changes are tracked in a synchronous fashion. By ‘Synchronous’ we…
