JohnWhat is Change Tracking?
Change Tracking (CT) is a lightweight solution to determine what DML (insert, update, delete) changes were made to a table. The scope of what changes should be tracked can be defined by an entire row or selected columns. Each time a record has changed, there is a synchronization version number that increases – which should be tracked to compare what set of data you have in the destination versus what is in the source. This should not be confused with the track changing technology Change Data Capture (CDC).
How to Enable Change Tracking
To enable CT for a table(s), it must first be enabled on the database:
- CHANGE_RETENTION: Defines the amount of time the CT information is kept. Anything older than the set value will be periodically be removed.
- AUTO_CLEANUP: Enables or disables the cleanup task that removes old CT data.
Now that the database has CT enabled, you can enable CT for each table you want:
- TRACK_COLUMNS_UPDATED: Defines whether the SQL Server Database engine should store extra information about which columns were updated.
Change Tracking Functions
To query what records have been altered in a database table, you will have to use CT functions to find the metadata needed.
| CHANGETABLE (CHANGES) | Returns tracking information for all changes to a table that have occurred since a specified version. |
| CHANGETABLE (VERSION) | Returns the latest change tracking information for a specified row. |
| CHANGE_TRACKING_MIN_VALID_VERSION() | Returns the minimum version that is valid for use in obtaining change tracking information from the specified table when you are using the CHANGETABLE function. |
| CHANGE_TRACKING_CURRENT_VERSION | Obtains a version that is associated with the last committed transaction. You can use this version the next time you enumerate changes by using CHANGETABLE. |
| CHANGE_TRACKING_IS_COLUMN_IN_MASK | Interprets the SYS_CHANGE_COLUMNS value that is returned by the CHANGETABLE(CHANGES …) function. |
| WITH CHANGE_TRACKING_CONTEXT | Enables the specification of a change context, such as an originator ID, when an application changes data. |
Query Changes using CHANGETABLE(CHANGES)
As previously stated, you’ll need to keep track of the synchronization version which will be passed in as a parameter in the CHANGETABLE(CHANGES) function. The MSDN example can be found here. Here is a simple example of how to pull changes from a table:
Hopefully that helps you get started.
Happy coding!
Nice layout and explanations! Alan and Tommy were talking about coding and how cool it is that they’re uncle John can code.
Tommy: “I bet you he can code a cool game.”
Al: “He probably already has dude! Just keeping it low key for now.”
Tommy: “Yea, I’m thinking about going into coding like Uncle John.”
True story…..it was on our way to Target to get Al a dress shirt.
I wish I was as cool as they think I am. However, it’s awesome to hear I can give someone hope to do something big!