How to return changes information of a change tracking table.

Problem: How to track changes from a change tracking enabled table.

Solution: CHANGETABLE (CHANGES), CHANGE_TRACKING_IS_COLUMN_IN_MASK

ChangeTables(changes) is system stored function that belongs to Change Tracking functions

Returns tracking information for all changes to a table that have occurred since a specified version.

Change_tracking_is_column_in_mask is another change tracking function that interprets the changes of the column values. Helps to determine whether the column is updated using sys_change_columns view.

Based on the output of the change_tracking_is_column_in_mask ,

0 the column is not the changes list.

1 the column is in change list.

Example:

In this article I am going to show you we can use change tracking.

Example 1: I this example, I would like to show how we can obtain all the changed data for a synchronization. In order to track the changes of a change tracking table, the change table change tracking function expects few parameters

•        Changes-return tracking of all changes
•        Table name of change tracking table.
•        Last_Sync_version to get the specific change version.

To know how to enable change tracking on a table please visit this article.

Enable Change Tracking

I have enabled change tracking on Orders table and run an update on orders table.

                update dbo.orders

                set isActive=1

                where unitid='m789'

 DECLARE @last_sync_version bigint; 

SET @last_sync_version =1

SELECT O.IsActive, c.[unitid], 

    c.SYS_CHANGE_VERSION as Cver, c.SYS_CHANGE_OPERATION as C_oper, 

    c.SYS_CHANGE_COLUMNS , c.SYS_CHANGE_CONTEXT  

FROM CHANGETABLE (CHANGES Orders, @last_sync_version) AS c 

    LEFT OUTER JOIN Orders AS O

        ON O.[Unitid] = c.[Unitid]

OUTPUT:

IsActive	unitid	Cver	C_oper	SYS_CHANGE_COLUMNS	SYS_CHANGE_CONTEXT
1	m789	3	U	0x0000000003000000	0x4D7943616368696E674170704944

From the above output the changes function gives us the version of the changes and column changes. 

Example 2:

In this example lets join the Change_tracking_is_column_in_mask to see which columns have changed and using the change table information to consolidate the list of information.

--returning change tracking version changes and columns changed.

DECLARE @PreviousVersion bigint

SET @PreviousVersion = 1

SELECT CTTable.Unitid, CTTable.SYS_CHANGE_OPERATION,

O.Loginid, O.IsActive, O.Callid,

[Loginid?] =

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Orders'),

'loginid', 'ColumnId'), SYS_CHANGE_COLUMNS),

[IsActive?] =

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Orders'),

'IsActive', 'ColumnId'), SYS_CHANGE_COLUMNS),

[Callid?] =

CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Orders'),

'Callid', 'ColumnId'), SYS_CHANGE_COLUMNS)

FROM CHANGETABLE (CHANGES Orders, @PreviousVersion) AS CTTable

LEFT OUTER JOIN Orders AS O

ON O.unitid = CTTable.unitid

WHERE CTTable.SYS_CHANGE_OPERATION = 'U'

OUTPUT:

Unitid	C_OPER	Loginid	IsActive	Callid	Loginid?	IsActive?	Callid?
m789	U	NULL	1	NULL	0	1	0

The above output will show us the column we have updated in the table and type of operation.