All Changes After A Specific Version
This is the most common reason for using
Change tracking. This query will return all the changes after a specific
version.
DECLARE @last_sync_version bigint;
SET @last_sync_version = 5;
SELECT emp.Name,
emp.Location,
emp.Salary,
ct.[ID],
ct.SYS_CHANGE_VERSION,
ct.SYS_CHANGE_OPERATION,
ct.SYS_CHANGE_COLUMNS,
ct.SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES dbo.tblEmployees, @last_sync_version)
AS ct
LEFT OUTER
JOIN dbo.tblEmployees
AS emp
ON emp.[ID] = ct.[ID]
The below output be generated from this
query.
If you are running a data warehouse, you
can pass the last synchronised version to retrieve the relevant data for the
data warehouse and then store the latest version so that you can pass that
value during next cycle of synchronization.
Change Tracking Functions
There are three additional Change Tracking
functions:
CHANGE_TRACKING_IS_COLUMN_IN_MASK
When a record is updated, SYS_CHANGE_COLUMNS
is updated with a mask value. CHANGE_TRACKING_IS_COLUMN_IN_MASK will allow you
to unmask the column name. This function requires two parameters, the first
parameter is the column Id and the second is the MASK value. This function will
inform you whether the given column was updated or not. You cannot get the
list of columns from this function.
The following query will list all the rows
which were updated after version 3 with an indication as to whether a particular
column has changed.
SELECT ID,
SYS_CHANGE_OPERATION,
SYS_CHANGE_COLUMNS,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Name’, ‘ColumnId’),
SYS_CHANGE_COLUMNS)IsNameChanged ,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Location’, ‘ColumnId’),
SYS_CHANGE_COLUMNS)IsLocationChanged ,
CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID(‘tblEmployees’), ‘Salary’, ‘ColumnId’),
SYS_CHANGE_COLUMNS)IsSalaryChanged
FROM CHANGETABLE
(CHANGES dbo.tblEmployees,3) as CT
Where SYS_CHANGE_OPERATION =’U’
Note that for ID = 1 row though the
SYS_CHANGE_COLUMNS is null all the columns have changed.
CHANGE_TRACKING_CURRENT_VERSION
CHANGE_TRACKING_CURRENT_VERSION returns a
version that is associated with the last committed transaction. Eg:
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
CHANGE_TRACKING_MIN_VALID_VERSION
This function returns the minimum version
that is valid for use in obtaining change tracking information for the
specified table. Eg:
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘tblEmployees’));
Comparison of Change Tracking and Change Data Capture in
Tables
|
Feature |
Change |
Change |
|
Synchronous |
Yes |
No |
|
Requires SQL Agent |
No |
Yes |
|
Forces full logging of some |
No |
Yes |
|
Prevents log truncation |
No |
Yes, until log records harvested |
|
Requires snapshot isolation |
Recommended |
No |
|
Requires separate tables to |
Yes |
Yes |
|
Requires primary key |
Yes |
Not by default |
|
Allows placement of tracking |
No |
Yes |
|
Potential for space |
Some |
Lots |
|
Automatic cleanup process |
Yes |
Yes |
|
Restrictions on DDL |
Yes |
No |
|
Permission required to enable |
Dbcreator |
Database owner |
|
Support Editions |
All Editions |
Enterprise |
The main point to stress here is the
availability of Change Tracking among all the SQL Server editions. Change
tracking feature is available even in the SQL Server express.
Conclusion
Change Tracking is light-weight option when
compared to CDC available in all editions of SQL Server. You can download
the script associated with the Change Tracking and if you want to clarify any
questions fell free to send a mail to dineshasanka@dbfriend.net
]]>