Home » SQL » Tracking Data Changes of DML Operations In SQL Server
SQL Server Log Analyzer

Tracking Data Changes of DML Operations In SQL Server

Stephen West ~ Modified: March 17th, 2023 ~ SQL ~ 6 Minutes Reading

changes of dml operations in sql

Summary: The agenda of this video is to simply understand the process of tracking data changes of DML operations in SQL server database. Just like other queries such as configuring windows firewall ports for SQL server settings, several types of error in SQL, etc, questions about the DML operations in SQL is also quite common among SQL users. Therefore this blog covers the explanation, enabling process, parameters, & other crucial aspects.

Table of Content

Overview

There are three newly introduced features of SQL server 2008, these features are considered auditing features of the server. A list of these features is given below:

  • Change Tracking
  • Change Data Capture
  • SQL Server Audit

In this blog, users will be able to find out complete information about change tracking, how to set up this feature within the server, and an overview of the change data capture feature.

Autopsy of  Change Tracking

Change tracking in SQL server enables users to find out only the changes, which are made on the user’s table. It recognizes the rows that were modified but does not provide information about the values that were altered. The following examples will help users to understand the concept of SQL server track data changes more clearly:

  • In a table, if you are inserting a row, then it will not give the detail (or values) that are inserted. However, it will only notify that a row was inserted into a particular table.
  • If you have made an updation or deletion within the table, then SQL server change tracking function is only to notify that any updation or deletion operation is performed into the table. It will not provide the values that were before and after the updation or deletion.

How to Enable Change Tracking In SQL Server?

There are some requirements that need to be fulfilled before enabling the SQL change tracking feature and they are mentioned below:

  • The Compatibility level of the server database should be set to 90 or above
  • There must be a primary key of the table where this feature is to be imposed
  • Enable snapshot isolation for ensuring information consistency of this feature

Tip: To enable snapshot isolation, users can use the following set of commands:

ALTER DATABASE AdventureWorks
SET READ_COMMITTED_SNAPSHOT ON
GO
ALTER DATABASE AdventureWorks
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

Enable Change Tracking on Table: The server users may enable the feature on each table of a database. This method will not affect the original structure of the table. One can run the following command in T-SQL for doing the same:

ALTER TABLE Person.Address
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

Now, the settings of each server table are altered in such a way that if any DML operation (Insertion, Deletion, and Updation) is performed on the server, then it will be tracked and stored for further reference.

Enable Change Tracking on Entire Database: Users can perform this enabling operation either by T-SQL commands or Management Studio. Hence, to enable change tracking with help of T-SQL, execute the following command set:

ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_REENTION = 5 DAYS, AUTO_CLEANUP = ON)

Following are the instruction for enabling this audit feature with help of SQL Server Management Studio:

  • Launch the management studio on your machine
  • Right-click on the database from Object Explorer
  • Now select Properties >> Change Tracking
  • Set the parameter as per your requirements

Description of Parameters Used In Commands for DML Operation in SQL Server:

  • CHANGE_TRACKING: It is an approach that provides a solution to track mechanisms for programs in SQL servers. Change tracking is not enabled on the table of SQL server therefore, this parameter is used to enable this operation on the server.
  • TRACK_COLUMNS_UPDATED: SQL server change tracking performance can be determined by tracking the rows of a table. If the set value is OFF, then it will only store the fact of whether the rows are modified or not. Moreover, if the set value is ON, then it stores the detail of the row that is updated, which can be read using CHANGE_TRACKING_IS_COLUMN_IN_MASK function.
  • CHANGE_RETENTION: This parameter is used to change the default value of the retention period. This period indicates the time duration when details of Change Tracking information are stored in the database. If AUTO_CLEANUP is enabled, then the entries older than X days will get automatically deleted.

    By default, this retention parameter is 2 days. The units of retention period appear only in SQL Server Management Studio and can be adjusted on the basis days, hours, or minutes. By default, the values are 2 days, 1 minute (minimum), and no maximum limit is there.

NOTE: If users are using this audit feature just for synchronization, then remember that value of the parameter should be longer than maximum time duration between 2 synchronizations.

AUTO_CLEANUP: This server parameter eliminates the tracking data from the database. It does not require any SQL server job or Agent and has its own mechanisms. Therefore, it can be used on other editions of SQL server.

Overview on Change Data Capture

In SQL server 2008, this audit feature is brought to make processes like transform, extract, and load easier. It also catches details about the data changes like insertion, deletion, and updation. Unlike SQL change tracking feature, this feature is used for capturing purposes and the data captured are different from each other.

NOTE: Change Data Capture and Change Tracking both are the audit features of SQL server but are different from each other. The functioning of both of them is a different and they capture different amount of information.

Also Read: Understand the SQL Server Clustering in Depth

Conclusion

The audit feature i.e., Change tracking feature is an outstanding feature to track DML operations in SQL server. This provides an ease to keep an eye on the modification made on the tables of a database. However, it is not similar to Change Data capture feature and hence, a different mechanism to analyze data within the server.