I have often been called upon to build incremental data capture solutions, and since it was introduced in SQL 2008 Change Tracking has been my favoured method of change identification. Unlike some methods, Change Tracking is very lightweight, with a minimal storage footprint and processing overhead.
Enabling Change Tracking
USE [master]
GO
ALTER DATABASE [Blog] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 7 DAYS)
GO
For the purposes of this demonstration I have created the following change tracking enabled table:
CREATE TABLE [CRM].[Applicant](
[ApplicantID] [int] IDENTITY(1,1) NOT NULL Primary Key,
[Forename] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[DateOfBirth] [date] NULL
)
GO
ALTER TABLE [CRM].[Applicant] ENABLE CHANGE_TRACKING
GO
Identifying Changes
CHANGETABLE(CHANGES) Allows you to identify all records that have changed since a known previous point, or every record that has changed within the retention period. This function takes 2 parameters; the name of the change tracked table, and an integer version identifier relating to the last identified change.
- SYS_CHANGE_VERSION - The version number of the latest change affecting the record.
- SYS_CHANGE_CREATION_VERSION - The version number relating to the insert of the record, if within the queried period.
- SYS_CHANGE_OPERATION - Whether the principle change was an insert (I), update (U) or Delete (D). Note that this is not necessarily the last change to the record.
- SYS_CHANGE_COLUMNS - If TRACK_COLUMNS_UPDATED is enabled, this is a binary value representing the fields that have been updated.
- SYS_CHANGE_CONTEXT - This field contains context information, which can be optionally supplied when performing updates.
- The primary key field(s) which uniquely identify the record in the change tracked table.
insert CRM.Applicant (Forename, Surname, DateOfBirth)
values ('Jon','Doe','1972-08-12'),
('Jane','Doe','1976-05-23')
insert CRM.Applicant (Forename, Surname, DateOfBirth)
values ('Dave','Grohl','1969-01-14')
Select *
from CHANGETABLE(Changes CRM.Applicant, 0) a
Next I'm going to update one of the records:
Update CRM.Applicant
Set Surname = 'Dough'
where ApplicantID = 2
;
Select *
from CHANGETABLE(Changes CRM.Applicant, 2) a
Select *
from CHANGETABLE(Changes CRM.Applicant, 0) a
This time applicant 2 has a change version of 3 showing that it was the most recent record to be modified, and a change creation version of 1 showing that the record was created within the period being examined, just not the latest statement affecting that record. Note also that the change operator this time indicates that the principle change within the period was the insertion of the record, not the update.
I'm going to perform 1 more update to demonstrate the change context:
Declare @Context varbinary(128)
Select @Context = convert(varbinary(128), 'This is an update')
;WITH CHANGE_TRACKING_CONTEXT (@Context)
Update CRM.Applicant
Set Surname = 'Dough'
where ApplicantID = 2
;
Select *, cast(SYS_CHANGE_CONTEXT as varchar(255))
from CHANGETABLE(Changes CRM.Applicant, 3) a
The change context field allows you to label particular changes, and can hold any data that can be converted to binary.
Next I would like to demonstrate how change tracking works when you have multiple tables. This time I create a change tracked Customer table with column tracking.
CREATE TABLE [CRM].[Customer](
[CustomerID] [int] IDENTITY(1,1) NOT NULL Primary Key,
[Forename] [varchar](50) NULL,
[Surname] [varchar](50) NULL,
[DateOfBirth] [date] NULL
)
;
ALTER TABLE [CRM].[Customer] ENABLE CHANGE_TRACKING WITH(TRACK_COLUMNS_UPDATED = ON)
;
insert CRM.[Customer] (Forename, Surname, DateOfBirth)
values ('Jon','Doe','1972-08-12'),
('Jane','Doe','1976-05-23')
;
Select *
from CHANGETABLE(Changes CRM.Customer, 0) a
With column tracking enabled it is possible to isolate just changes affecting individual columns, by using the CHANGE_TRACKING_IS_COLUMN_IN_MASK() function. In the following example I am modifying Forename field on one record, and the surname field on the other. I am then selecting all changes, just those where the Forename have been updated, and finally just those where the Surname was updated.
Update CRM.[Customer]
Set Forename = 'John'
where CustomerID = 1
;
Update CRM.[Customer]
Set Surname = 'John'
where CustomerID = 2
;
Select *
from CHANGETABLE(Changes CRM.Customer, 5) a
;
Select *
from CHANGETABLE(Changes CRM.Customer, 5) a
where CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('CRM.Customer'),'Forename', 'ColumnId'), a.SYS_CHANGE_COLUMNS) = 1
;
Select *
from CHANGETABLE(Changes CRM.Customer, 5) a
where CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('CRM.Customer'),'Surname', 'ColumnId'), a.SYS_CHANGE_COLUMNS) = 1
;
Select SYS_CHANGE_VERSION, b.*
from CHANGETABLE(Changes CRM.Customer, 5) a
join [CRM].[Customer] b on a.CustomerID = b.CustomerID
;
Synchronising Change Tracking
Select v.SYS_CHANGE_VERSION, c.*
from [CRM].[Customer] c
cross apply CHANGETABLE(VERSION CRM.Customer, (CustomerID), (c.CustomerID)) v
;
Happy SQLing