How to read SQL Server Change Tracking results

As described in the previous article of this series, What is SQL Server Change Tracking and how to set it up, SQL Server Change Tracking shows only the primary key column value for the changed rows, and the type of change – INSERT, DELETE, or UPDATE. Here we will explain change tracking functions, show code examples and demonstrate how to read the Change Tracking results

Change tracking functions

There is no out-of-the-box option to see the change tracking information. To see the rows that were changed and change details, use change tracking functions in T-SQL queries [1]

The CHANGETABLE(CHANGES) function shows all changes to a table that have occurred after the specified version number. A version number is associated with each changed row. Whenever there is a change on a table where Change tracking is enabled, the database version number counter is increased

The CHANGETABLE (VERSION) function “returns the latest change tracking information for a specified row“ [2]

SELECT * FROM CHANGETABLE(CHANGES <table_name>, <version>) AS ChTbl

Note that the table used in the CHANGETABLE function has to be aliased

The CHANGE_TRACKING_CURRENT_VERSION() function retrieves the current version number, i.e. the version number of the last committed transaction

SELECT NewTableVersion =  CHANGE_TRACKING_CURRENT_VERSION()

Returns NULL if Change tracking is not enabled, an integer otherwise. The minimal value returned is 0. In the example above, it returns 17

The CHANGE_TRACKING_MIN_VALID_VERSION() function shows the minimum version number that can be used to get change tracking information for the specified table using the CHANGETABLE function

SELECT MinVersion = 
CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('Person.Address')) 

In the example above shows 14

The CHANGE_TRACKING_IS_COLUMN_IN_MASK function shows whether the specific column was updated or not. If it was updated, the value is 1, otherwise 0. It can only be used if the TRACK_COLUMNS_UPDATED parameter for enable change tracking on a table is set to ON

Reading the Change Tracking results

Here’s an example for the data changes executed on the Person.Address table

1. Execute

   SELECT TableVersion = CHANGE_TRACKING_CURRENT_VERSION();
   SELECT * FROM Person.Address;
   

   The Change Tracking results show that this is the first version of the tracked table and the current records in the Person.Address table

   

2. Modify the records in the Person.Address table, either using T-SQL or editing rows in the SQL Server Management Studio grid. The changes I made are highlighted – I updated the rows with AddressIDs 1, 5 and 2, in that order

   

3. I added a row. Note that the AddressID is 32522

   

4. I deleted the row I added in the previous step

   DELETE Person.Address WHERE addressid = 32522;

5. To read the Change Tracking results, execute

   SELECT
   NewTableVersion = CHANGE_TRACKING_CURRENT_VERSION()
   
   SELECT
   ChVer = SYS_CHANGE_VERSION,
   ChCrVer = SYS_CHANGE_CREATION_VERSION,
   ChOp = SYS_CHANGE_OPERATION,
   AddressID
   FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;
   

The results are:

The values shown in the ChOp column indicate the changes made. ‘U’ stands for update, ‘D’ for delete, ‘I’ for insert. There are three updates on the rows with AddressID 1, 2, and 5 and deletion of the row with AddressID = 32522. There is no clear indication that the 32522 row was first inserted, but according to the Change Creation Version (ChCrVer) and Change Version (ChVer) values 5 and 6, there were 2 changes. The second one was a delete, but we don’t know what the first one was

I re-inserted the same 32522 row and refreshed the results

As expected, the current version number is 7, increased by 1 as there was one more change. But the information about the 32522 row is even vaguer when it comes to row history

Tracking individual column updates

If you add the SYS_CHANGE_COLUMNS column to the query, you will get the binary number of the column that was changed. The value is NULL only if the column change tracking option is not enabled, or all columns expect the primary key in the row were updated

“Column tracking can be used so that NULL is returned for a column that has not changed. If the column can be changed to NULL, a separate column must be returned to indicate whether the column changed.” [2]

To present column changes in a more readable format, use the CHANGE_TRACKING_IS_COLUMN_IN_MASK function. It has to be called for each column individually. In the following example, I’ll check whether the columns AddressLine1 and AddressLine2 have been modified

SELECT
ChVer = SYS_CHANGE_VERSION,
ChCrVer = SYS_CHANGE_CREATION_VERSION,
ChOp = SYS_CHANGE_OPERATION,
AddLine1_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine1', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddLine2_Changed = CHANGE_TRACKING_IS_COLUMN_IN_MASK
    (COLUMNPROPERTY(OBJECT_ID('Person.Address'), 'AddressLine2', 'ColumnId')
    ,ChTbl.sys_change_columns),
AddressID
FROM CHANGETABLE(CHANGES Person.Address, 1) AS ChTbl;

The value 1 in the AddLine1_Changed and AddLine2_Changed columns indicates that the specific column has been changed

As shown, SQL Server Change Tracking is a synchronous process that can be easily configured on your tables. It is supported in all SQL Server editions, so there are no additional licensing expenses. It can be utilized in applications designed for one-way and two-way data synchronization, as it can seamlessly synchronize several databases, each at a different time

The Change Tracking feature is not designed to return all information about the changes you might need, it’s designed to be a light auditing solution that indicates whether the row has been changed or not. It shows the ID of the row changed, even the specific column that is changed. What this feature doesn’t provide are the details about the change. You can match the change information to the database snapshot and the live database to find out more about the changes, but this requires additional coding and still doesn’t bring all the information that might be needed for auditing

Change tracking doesn’t answer the “who”, “when”, and “how” questions. Also, if there were multiple changes on a specific row, only the last one is shown. There is no user-friendly GUI that displays the results in just a couple of clicks. To see the change tracking records, you have to write code and use change tracking functions

The execution of the SELECT statements and database object access is not tracked. These events have nothing to do with data changes, but as SQL DBAs request these features when it comes to auditing, it should be mentioned

The SQL Server Change Tracking feature provides a lot of useful information for data tracking and synchronization. It is designed to track changes and provide minimum details. If the information about the changes it provides does not fulfill all your auditing requests, there are other auditing solutions. In the sequel to this article, we’ll analyze other SQL Server auditing features and ApexSQL Audit

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

Useful resources:

MSDN – Enable and Disable Change Tracking (SQL Server)
MSDN – Change Tracking
MSDN – Comparing Change Data Capture and Change Tracking