Change Tracking of SQLServer

1.Enable the change tracking at the database level.

ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = ON;

By Default retention is 2 dyas with auto clean up on.We can use below SQL to check it.

SELECT * FROM sys.change_tracking_databases

database_id  is_auto_cleanup_on      retention_period         retention_period_units         retention_period_units_desc
-----------      ------------------         ----------------         ---------------------------------     -----------------------------
6                    1                                  2                           3                                        DAYS

SQLServer allow us to sepcify the retention and the auto clean up.
When try to enable the change tracking if it already enabled, then need to disable it first

ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = OFF
GO
ALTER DATABASE AdventureWorks2008 SET CHANGE_TRACKING = ON (AUTO_CLEANUP=ON, CHANGE_RETENTION=1 hours);

SELECT   *  FROM sys.change_tracking_databases
database_id         is_auto_cleanup_on       retention_period       retention_period_units       retention_period_units_desc
-----------------   --------------------------- ---------------------  -----------------------------  --------------------------------
6                              1                              1                             2                                 HOURS

Note:
If you got error message saying "Change tracking is enabled for one or more tables in database 'AdventureWorks2008'.
Disable change tracking on each table before disabling it for the database.
Use the sys.change_tracking_tables catalog view to obtain a list of tables for which change tracking is enabled."

use below SQL to find out the table and disable it first. refer to section 2 on how to disable the change tracking on table level.

select object_name(object_id),* from sys.change_tracking_tables

After enable the Change tracking on database level ,then SQLServer create a system internal table sys.syscommittab.
Which we can not query use SELECT except the admin connected in the DAC mode.
But we can see what columns the table have via query th sys.all_columns catalog.

SELECT object_id, name
FROM sys.all_columns
WHERE object_id = OBJECT_ID('sys.syscommittab');

object_id               name 
----------------  -------------------
2089058478       commit_ts
2089058478       xdes_id 
2089058478       commit_lbn
2089058478       commit_csn
2089058478       commit_time
2089058478       dbfragid

 

--Column Name-- --Type---- -----------Description------------------------------------
commit_ts          BIGINT                The ascending CSN for the transaction
xdes_id              BIGINT                The internal identifi er for the transaction
commit_lbn         BIGINT                The log block number for the transaction
commit_csn        BIGINT                 The instance-wide sequence number for the transaction
commit_time       DATETIME             The time the transaction was committed
dbfragid INT      reserved for future use

List All the change tracking commit history.

select * from sys.dm_tran_commit_table

 

2. Enable the change tracking on table level.

ALTER TABLE HumanResources.Employee ENABLE CHANGE_TRACKING

 

SQLServer allow track the update on each column, you need to set option when enable table level tracking.

Disable it first when try to change the option.

ALTER TABLE HumanResources.Employee DISABLE CHANGE_TRACKING

GO

ALTER TABLE HumanResources.Employee ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);

list all the tables that already enabled the change tracking feature.

select object_name(object_id),* from sys.change_tracking_tables

For each table, SQLServer will create a system internal table in temp database.

select * from sys.objects where NAME like 'change_tracking_%'

3. Examples on how to use the change tracking feature.

user can set some identifier content on the change contest 

DECLARE @context VARBINARY(128) =
CONVERT(VARBINARY(128), SUSER_SNAME());

WITH CHANGE_TRACKING_CONTEXT(@context)
UPDATE AdventureWorks2008.HumanResources.Employee
SET
JobTitle = 'Production Engineer'
WHERE
BUSINESSENTITYID=290

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK (
(OBJECT_ID('AdventureWorks2008.HumanResources.Employee'), 'JobTitle', 'ColumnId'),
0x0000000006000000);

select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('AdventureWorks2008.HumanResources.Employee'));

select CHANGE_TRACKING_CURRENT_VERSION()

declare @last_version bigint=1;
select * from changetable(changes AdventureWorks2008.HumanResources.Employee,@last_version)
c left outer join AdventureWorks2008.HumanResources.Employee d on c.businessentityid=d.businessentityid

SYS_CHANGE_VERSION SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION SYS_CHANGE_COLUMNS SYS_CHANGE_CONTEXT BusinessEntityID BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
-------------------- --------------------------- -------------------- ----------------------------------------------------------------------- ---------------- ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------- -------------------------------------------------- ---------- ------------- ------ ---------- ------------ ------------- -------------- ----------- ------------------------------------ -----------------------
3 NULL U 0x0000000006000000 0x7000720069006E0063006500730073006400 290 290 134219713 adventure-works\ranjit0 0x95EF 3 Production 88888 1969-10-31 S M 2006-07-01 1 34 37 1 604213F9-DD0F-43B4-BDD2-C96E93D3F4BF 2008-07-31 00:00:00.000

SELECT
c.SYS_CHANGE_VERSION,
c.SYS_CHANGE_CONTEXT,
e.*
FROM AdventureWorks2008.HumanResources.Employee e
CROSS APPLY CHANGETABLE
(
VERSION AdventureWorks2008.HumanResources.Employee,
(BusinessEntityId),
(e.BusinessEntityId)
) c where c.SYS_CHANGE_VERSION is not null;

SYS_CHANGE_VERSION SYS_CHANGE_CONTEXT BusinessEntityID NationalIDNumber LoginID OrganizationNode OrganizationLevel JobTitle BirthDate MaritalStatus Gender HireDate SalariedFlag VacationHours SickLeaveHours CurrentFlag rowguid ModifiedDate
-------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------- ---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ----------------- -------------------------------------------------- ---------- ------------- ------ ---------- ------------ ------------- -------------- ----------- ------------------------------------ -----------------------
3 0x7000720069006E0063006500730073006400 290 134219713 adventure-works\ranjit0 0x95EF 3 Production 88888 1969-10-31 S M 2006-07-01 1 34 37 1 604213F9-DD0F-43B4-BDD2-C96E93D3F4BF 2008-07-31 00:00:00.000