Why would you pick Change Tracking as a technology to use?
This allows you to detect changes in a lightweight manner via T-SQL functions to extract information about DML changes to Change Tracking enabled tables. Change Data Capture is more about auditing or creating a historical view utilizing the Transaction Log and Temporal Tables are the next step up from there which became available in 2016 versions of SQL Server. Change Tracking is primarily used for finding only things that have changed. Not necessarily why, how, or who changed it, but what has changed and what it is now.
So why would you want this technology implemented? I find this technology is best suited for tasks where I want as light of a footprint as possible and I want to bring over incremental changes.
To get started, I have listed some reading material for you so I don’t reinvent the wheel.
The documentation is thorough and listed in the order I would recommend to read when learning how to implement Change Tracking in your environment.
Overview of CT and CDC –11 min
Here’s code I used when learning how to work with CT on AdventureWorks 2016.
Before using it, I need to enable CT – So first, we must do it at a DB level.
ALTER DATABASE AdventureWorks2016CTP3
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
-- NOTE! Look at the CHANGE_RETENTION and AUTO_CLEANUP settings here.
-- Configure and adjust those values to fit your criteria.
Second, I need to enable it at a table level afterwards.
USE [AdventureWorks2016CTP3]; ALTER TABLE Person.Contact ENABLE CHANGE_TRACKING WITH (TRACK_COLUMNS_UPDATED = ON);
I wanted to generate some changes to look at.
USE [AdventureWorks2016CTP3]; UPDATE [Person].[Person] SET title = 'Mr.' WHERE BusinessEntityID = 7; UPDATE [Person].[Person] SET title = 'Mr.' WHERE BusinessEntityID = 2;
Viewing my change results.
DECLARE @last_sync_version BIGINT;
SET @last_sync_version = 1;
SELECT P.BusinessEntityID, Title, c.*
FROM person.person p
CROSS APPLY CHANGETABLE(VERSION [person].[Person], (BusinessEntityID), (p.BusinessEntityID)) AS C
WHERE SYS_CHANGE_VERSION IS NOT NULL;
Additional lessons learned testing CT
- All tables must have a primary key.
- Not be involved in partitioning.
- You cannot alter constraints unless CT is disabled first.
Additional resources I read through while learning CT
Mike Byrd on Logical Read: SQL Server Change Tracking: Near Bullet-Proof ETL (Part 1)
Tim Mitchell of TimMitchell.net: Using Change Tracking in SSIS
Now that we have learned a little bit about CT, here are some goals and use cases I wanted to develop proof of concepts for. Let’s start by building our environment:
/*
(1b)
CREATE THE DATABASE
*/
CREATE DATABASE [CTPOC] CONTAINMENT = NONE
ON PRIMARY
(
NAME = N'CTPOC',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SHAULIS\MSSQL\DATA\CTPOC.mdf',
SIZE = 3072KB,
FILEGROWTH = 1024KB
)
LOG ON
(
NAME = N'CTPOC_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SHAULIS\MSSQL\DATA\CTPOC_log.ldf',
SIZE = 1024KB,
FILEGROWTH = 10%
);
GO
USE [CTPOC];
GO
CREATE SCHEMA [archive]
GO
/*
(2)
CREATE THE VERSION TRACKING TABLE
*/
USE CTPOC;
CREATE TABLE [dbo].[VersionTracking]
([ID] [BIGINT] IDENTITY(1, 1) NOT NULL,
[Table_Name] [VARCHAR](255) NOT NULL,
[Table_Version] [BIGINT] NOT NULL,
[Table_LastVersion] [BIGINT] NOT NULL,
[Ignore_Table] [BIT] NOT NULL,
[CT_Disabled] [BIT] NOT NULL,
[CreatedDate] [DATETIME2](2) NOT NULL,
[CreatedBy] [VARCHAR](255) NOT NULL,
[UpdatedDate] [DATETIME2](2) NOT NULL,
[UpdatedBy] [VARCHAR](255) NOT NULL,
[HostName] [VARCHAR](255) NOT NULL,
[AppName] [VARCHAR](255) NOT NULL,
PRIMARY KEY CLUSTERED([ID] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT((0)) FOR [Table_Version];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT((0)) FOR [Table_LastVersion];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT((1)) FOR [Ignore_Table];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT((1)) FOR [CT_Disabled];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(GETDATE()) FOR [CreatedDate];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(SUSER_SNAME()) FOR [CreatedBy];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(GETDATE()) FOR [UpdatedDate];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(SUSER_SNAME()) FOR [UpdatedBy];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(HOST_NAME()) FOR [HostName];
GO
ALTER TABLE [dbo].[VersionTracking]
ADD DEFAULT(APP_NAME()) FOR [AppName];
GO
/*
(3)
CREATE THE VERSION TRACKING HISTORY TABLE
*/
USE [CTPOC];
GO
/****** Object: Table [archive].[VersionTracking] Script Date: 7/2/2018 10:44:29 AM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [archive].[VersionTracking]
(
[ArchiveID] [BIGINT] IDENTITY(1, 1) NOT NULL,
[ID] [BIGINT] NOT NULL,
[Table_Name] [VARCHAR](255) NOT NULL,
[Table_Version] [BIGINT] NOT NULL,
[Table_LastVersion] [BIGINT] NOT NULL,
[Ignore_Table] [BIT] NOT NULL,
[CT_Disabled] [BIT] NOT NULL,
[CreatedDate] [DATETIME2](2) NOT NULL,
[CreatedBy] [VARCHAR](255) NOT NULL,
[UpdatedDate] [DATETIME2](2) NOT NULL,
[UpdatedBy] [VARCHAR](255) NOT NULL,
[HostName] [VARCHAR](255) NOT NULL,
[AppName] [VARCHAR](255) NOT NULL,
PRIMARY KEY CLUSTERED ([ArchiveID] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]
) ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO
/*
(4)
CREATE THE TEST TABLES
*/
USE [CTPOC];
GO
IF OBJECT_ID('dbo.TestRecords1', 'U') IS NOT NULL
DROP TABLE dbo.TestRecords1;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[TestRecords1]
([ID] [INT] IDENTITY(1, 1) NOT NULL,
[FakeVarchar] [VARCHAR](255) NULL,
[FakeInt] [INT] NULL,
[FakeDate] [DATE] NULL,
PRIMARY KEY CLUSTERED([ID] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO
USE [CTPOC];
GO
IF OBJECT_ID('dbo.TestRecords1Dest', 'U') IS NOT NULL
DROP TABLE dbo.TestRecords1Dest;
GO
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
SET ANSI_PADDING ON;
GO
CREATE TABLE [dbo].[TestRecords1Dest]
([DestID] [INT] IDENTITY(1, 1) NOT NULL,
[ID] [INT] NULL,
[FakeVarchar] [VARCHAR](255) NULL,
[FakeInt] [INT] NULL,
[FakeDate] [DATE] NULL,
PRIMARY KEY CLUSTERED([DestID] ASC)
WITH(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY];
GO
SET ANSI_PADDING OFF;
GO
-- Enable CT at DB Level
ALTER DATABASE CTPOC SET CHANGE_TRACKING = ON(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
-- Enable CT at Table Level
USE CTPOC;
ALTER TABLE dbo.TestRecords1 ENABLE CHANGE_TRACKING
WITH(TRACK_COLUMNS_UPDATED = ON);
/*
(5)
POPULATE THE VERSION TRACKING TABLE
*/
-- Set Database
USE CTPOC;
-- Declare variable table to hold table names
DECLARE @variablenametable TABLE
(Id INT
PRIMARY KEY IDENTITY(1, 1),
variablename VARCHAR(255)
);
-- Holds the table name to stop
DECLARE @variablename VARCHAR(255);
-- Dynamic SQL holder
DECLARE @variablenamekill NVARCHAR(MAX);
-- Holds the ID of the table in the loop
DECLARE @ID INT= 0;
-- Insert names of table into the table variable
INSERT INTO @variablenametable(variablename)
SELECT name
FROM CTPOC.dbo.sysobjects
WHERE xtype = 'U';
MERGE dbo.VersionTracking AS Target
USING
(
SELECT DISTINCT
variablename AS table_name,
CASE
WHEN tt.min_valid_version >= 0
THEN 0
ELSE 1
END AS CT_Disabled
FROM @variablenametable vn
LEFT JOIN dbo.VersionTracking vt ON vn.variablename = vt.Table_Name
LEFT JOIN sys.objects o ON vn.variablename = o.name
LEFT JOIN sys.change_tracking_tables tt ON o.object_id = tt.object_id
) AS Source
ON(Target.table_name = Source.table_name)
WHEN MATCHED
THEN UPDATE SET
CT_Disabled = Source.CT_Disabled
WHEN NOT MATCHED
THEN
INSERT(table_name,
CT_Disabled)
VALUES
(Source.table_name,
Source.CT_Disabled
);
/*
(6)
CREATE TEST RECORDS
*/
USE CTPOC;
INSERT INTO dbo.TestRecords1
(
FakeVarchar,
FakeInt,
FakeDate
)
VALUES
('A', 1, '2018-01-01'),
('B', 2, '2018-02-01'),
('C', 3, '2018-03-01');
Now that we are all set up, here’s our use case scenario:
Use Case: Server A has records, Server B needs those records.
-
- With as minimal administration and computing resources, we need to bring the data from A to B.
-
- It must run quickly.
-
- It must be accurate.
- Data changes on A and we may need to update those records or remove those records from B.
Solution: CT provides a minimal approach to bring only changed records. Fundamentally, this is a lower cost and more efficient method of using DateTime to store the latest changes and pull over those changes from A to B. Depending on the scope of the project, there could be a lot of development work. This could be mitigated with dynamic SQL or SSIS or BIML.
Scenario A (Basics)
Table A has records that have been inserted or updated between CT runs.
Requirements
- Bring back only the latest rows.
- Bring back only the latest rows of all changes after adjusting the version table to be 0, indicating a full pull of data.
Test 1 for Scenario A
- Insert three records.
- CT should evaluate that there are three new rows, it should pull three rows back to insert or update the destination table. It should then update the version table with the appropriate values.
- We will then update a record and reset the changes table to a value of 0. CT should bring back a snapshot of our current source table.
-- Clear testing instance
USE CTPOC;
TRUNCATE TABLE dbo.TestRecords1;
TRUNCATE TABLE dbo.TestRecords1Dest;
UPDATE dbo.VersionTracking
SET
table_version = 0
WHERE Table_Name = 'TestRecords1';
-- Set new values for testing
INSERT INTO dbo.TestRecords1
(FakeVarchar,
FakeInt,
FakeDate
)
VALUES
('A',
1,
'2018-01-01'
),
('B',
2,
'2018-02-01'
),
('C',
3,
'2018-03-01'
);
-- Run CT process, expect three rows to migrate over.
-- Declaring variables to hold CT information
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
-- Setting CT variable to database current version
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
-- Setting another CT variable but to the table version.
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
-- Allows us to insert or update with the merge statement
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
-- Join to system table allows us to filter results
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
-- Limit information back to most current version of change in table
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
-- Set VersionTracking to last update of table.
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
Let’s view the results from our test:
/* (1) Scenario A: Test 1 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
We can see the VersionTracking table was updated to the last version of the changes we just made.
/* (2) Scenario A: Test 1 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
The TestRecords1 table and the TestRecords1Dest table were both empty to start and we inserted three rows into the TestRecords1 table.
/* (3) Scenario A: Test 1 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
We had enabled Change Tracking and used a MERGE statement which was joined to the Change Tracking table. This brought back the most recent changes that were new to the TestRecords1Dest table.
Let’s update two records, set the VersionTracking to 0 to indicate we are doing a full pull and see if we match TestRecords1 to TestRecordsDest1 after using CT and Merge.
USE CTPOC;
-- Change two records, setting us up for the third objective for Test 1.
UPDATE dbo.TestRecords1
SET FakeDate = '2020-04-14'
WHERE ID = '2';
GO
UPDATE dbo.TestRecords1
SET FakeDate = '2020-08-18'
WHERE ID = '3';
GO
-- Set VersionTracking to 0 to indicate a full pull
UPDATE dbo.VersionTracking
SET
table_version = 0
WHERE Table_Name = 'TestRecords1';
GO
-- Run CT process, expect three rows to migrate over.
-- Declaring variables to hold CT information
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
-- Setting CT variable to database current version
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
-- Setting another CT variable but to the table version.
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
-- Allows us to insert or update with the merge statement
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
-- Join to system table allows us to filter results
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
-- Limit information back to most current version of change in table
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
-- Set VersionTracking to last update of table.
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
Let’s view the final results of our test. We expect our source table should match our destination table and our VersionTracking table should indicate it has the latest version.
/* (4) Scenario A: Test 1 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
/* (5) Scenario A: Test 1 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (6) Scenario A: Test 1 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
Great success! Our source and destination match after a full load and resetting the VersionTracking table to 0 and we updated our VersionTracking table to the newest version.
Test 2 for Scenario A
- Update three records.
- CT should evaluate that there are three changed rows, it should pull three rows back to insert or update the destination table. It should then update the version table with the appropriate table.
- Update one row.
- Update this row again but with a different column.
- Update this row a third time, again using a different column from the previous two times.
- CT process runs.
- It should pull over one row with the latest changes.
USE CTPOC;
-- Move onto second piece of test. Update three rows in three different transactions.
UPDATE dbo.TestRecords1
SET
fakevarchar = 'Z'
WHERE id = 1;
GO
UPDATE dbo.TestRecords1
SET
fakevarchar = 'Y'
WHERE id = 2;
GO
UPDATE dbo.TestRecords1
SET
fakevarchar = 'X'
WHERE id = 3;
GO
/* (1) Scenario A: Test 2 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
We can see the VersionTracking table is currently at version 4 for our table TestRecords1.
/* (2) Scenario A: Test 2 - Change Tracking Current Version View current tracking version */ USE CTPOC; SELECT CHANGE_TRACKING_CURRENT_VERSION() as 'Tracking Current Version';
The database is set to change tracking version 7, indicating our table is not currently up to date.
/* (3) Scenario A: Test 2 - TestRecords1 Table View TestRecords1 table - shows records we updated */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
Here’s our current source table after the updates we ran above. Now we commence the second part of the first objective, moving the data to our destination from the source.
USE CTPOC;
-- Run CT process, expect one row to migrate over.
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
/* (4) Scenario A: Test 2 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
The table has updated to match the latest Change Tracking version.
/* (5) Scenario A: Test 2 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (6) Scenario A: Test 2 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
Now let’s update one row three times in three separate transactions by updating a different column each time.
-- Update one row in the source table three times on different columns to
-- see what data is moved over to the destination. It is expected to
-- look like exact copies of each other.
USE CTPOC;
UPDATE dbo.TestRecords1
SET
fakevarchar = 'Q'
WHERE id = 1;
GO
UPDATE dbo.TestRecords1
SET
FakeInt = 1337
WHERE id = 1;
GO
UPDATE dbo.TestRecords1
SET
FakeDate = '1915-1-1'
WHERE id = 1;
GO
-- Run CT process, expect one row to migrate over.
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
/* (7) Scenario A: Test 2 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
Our table has again updated to be the latest Change Tracking version.
/* (8) Scenario A: Test 2 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (9) Scenario A: Test 2 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
CT does not treat the updates any differently if it’s updating different rows or the same row multiple times between runs, it will pull back our latest change. This is illustrated by the above SQL and pictures, the source again matches the destination.
Scenario B (Handling Deletes)
Table A has records that have been inserted or updated or deleted between CT runs.
Requirements
- Bring back only the latest rows of all changes since the last CT process.
- Bring back only the latest rows of all changes after adjusting the version table to be 0, indicating a full pull of data.
- Differentiate between insert/update/delete, process records in correct order so no data discrepancies exist between Table A and Table B.
Test 1 for Scenario B
- Insert three records.
- Update one of the three rows.
- Update a different row.
- Delete this row we just updated.
CT Should only pull back two records. One is an original record as it was untouched, the second should be the modified record. The third record should not reach the destination table.
USE CTPOC;
-- Clear testing instance
TRUNCATE TABLE dbo.TestRecords1;
TRUNCATE TABLE dbo.TestRecords1Dest;
UPDATE dbo.VersionTracking
SET
table_version = 0
WHERE Table_Name = 'TestRecords1';
-- Set new values for testing
INSERT INTO dbo.TestRecords1
(FakeVarchar,
FakeInt,
FakeDate
)
VALUES
('A',
1,
'2018-01-01'
),
('B',
2,
'2018-02-01'
),
('C',
3,
'2018-03-01'
);
INSERT INTO dbo.TestRecords1Dest
(ID,
FakeVarchar,
FakeInt,
FakeDate
)
SELECT ID,
FakeVarchar,
FakeInt,
FakeDate
FROM TestRecords1;
UPDATE dbo.TestRecords1
SET
fakevarchar = 'Z'
WHERE id = 1;
UPDATE dbo.TestRecords1
SET
fakeint = '9001'
WHERE id = 2;
DELETE dbo.TestRecords1
WHERE id = 2;
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
-- Records that need to be inserted or updated.
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES TestRecords1, @bigint) AS C ON c.id = t.ID
WHERE sys_change_version >=
(
SELECT TOP 1 table_version
FROM VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(target.id = source.id)
WHEN MATCHED
THEN UPDATE SET
id = source.id,
FakeVarchar = source.FakeVarchar,
FakeInt = source.FakeInt,
FakeDate = source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(source.id,
source.FakeVarchar,
source.FakeInt,
source.FakeDate
);
-- Records that should be deleted
DELETE dbo.TestRecords1Dest
WHERE ID IN
(
SELECT c.id
FROM CHANGETABLE(CHANGES TestRecords1, @bigint) AS C
WHERE sys_change_operation = 'D'
AND c.id NOT IN
(
SELECT t.id
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES TestRecords1, @bigint) AS C ON c.id = t.ID
WHERE sys_change_version >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
)
);
-- Set version to latest pull
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
-- View result set aftewards.
-- Resolves Scenario B Test 1
We’ve just inserted three records, updated two of them, and then deleted one of the updated records. Let’s look at our current version in CT as well as our VersionTracking table and source/destination table. The destination should match our source.
/* (1) Scenario B: Test 1 - Change Tracking Current Version View current tracking version */ USE CTPOC; SELECT CHANGE_TRACKING_CURRENT_VERSION() as 'Change Tracking Current Version';
/* (2) Scenario B: Test 1 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
As expected, the database Change Tracking version matches the table Change Tracking version.
/* (3) Scenario B: Test 1 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (4) Scenario B: Test 1 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
Both tables match! Let’s move on to our final proof of concept test.
Test 2 for Scenario B
- Insert three records.
- CT process runs.
- We will now use DML statements on three rows.
- Update one of the three rows.
- Update a different row.
- Delete the other row that has had no changes.
- CT process runs.
- Two rows should be processed over. One row should be flagged as ready for deletion and the old record should be removed from the destination table.
- Insert the record that was deleted back to the original table.
- Run an update statement on the record that was deleted.
- CT process runs.
- One row should be pulled over.
USE CTPOC;
-- Clear testing instance
TRUNCATE TABLE dbo.TestRecords1;
TRUNCATE TABLE dbo.TestRecords1Dest;
UPDATE dbo.VersionTracking
SET
table_version = 0
WHERE Table_Name = 'TestRecords1';
-- Set new values for testing
INSERT INTO dbo.TestRecords1
(FakeVarchar,
FakeInt,
FakeDate
)
VALUES
('A',
1,
'2018-01-01'
),
('B',
2,
'2018-02-01'
),
('C',
3,
'2018-03-01'
);
INSERT INTO dbo.TestRecords1Dest
(ID,
FakeVarchar,
FakeInt,
FakeDate
)
SELECT ID,
FakeVarchar,
FakeInt,
FakeDate
FROM dbo.TestRecords1;
UPDATE dbo.TestRecords1
SET
fakevarchar = 'Z'
WHERE id = 1;
UPDATE dbo.TestRecords1
SET
fakeint = '9001'
WHERE id = 2;
DELETE dbo.TestRecords1
WHERE id = 3;
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
-- Records that need to be inserted or updated.
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
-- Records that should be deleted
DELETE dbo.TestRecords1Dest
WHERE ID IN
(
SELECT C.id
FROM CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C
WHERE SYS_CHANGE_OPERATION = 'D'
AND C.id NOT IN
(
SELECT t.id
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
)
);
-- Set version to latest pull
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
-- View result set aftewards.
-- TestRecords1Dest should contain two rows
Once more, our source and destination should match as should the database Change Tracking version and our table Change Tracking version.
/* (1) Scenario B: Test 2 - Change Tracking Current Version View current tracking version */ USE CTPOC; SELECT CHANGE_TRACKING_CURRENT_VERSION() as 'Change Tracking Current Version';
/* (2) Scenario B: Test 2 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
/* (3) Scenario B: Test 2 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (4) Scenario B: Test 2 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
The table version updates as expected and the source matches the destination with only two rows each while keeping the changes to the other two rows we updated.
Now let’s insert the old record we deleted and let’s update it after inserting it. We’ll see how the source and destination table handle this transaction.
-- Set record for testing
SET IDENTITY_INSERT dbo.TestRecords1 ON;
INSERT INTO TestRecords1
(ID,
FakeVarchar,
FakeInt,
FakeDate
)
VALUES
(3,
'C',
3,
'2018-03-01'
);
SET IDENTITY_INSERT dbo.TestRecords1 OFF;
UPDATE dbo.TestRecords1
SET
FakeDate = '2020-04-24'
WHERE id = 3;
GO
-- Run test
DECLARE @bigint BIGINT;
DECLARE @newbigint BIGINT;
SET @newbigint =
(
SELECT CHANGE_TRACKING_CURRENT_VERSION()
);
SET @bigint =
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
);
-- Records that need to be inserted or updated.
MERGE dbo.TestRecords1Dest AS Target
USING
(
SELECT t.id,
t.FakeVarchar,
t.FakeInt,
t.FakeDate
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
) AS Source
ON(Target.id = Source.id)
WHEN MATCHED
THEN UPDATE SET
id = Source.id,
FakeVarchar = Source.FakeVarchar,
FakeInt = Source.FakeInt,
FakeDate = Source.FakeDate
WHEN NOT MATCHED
THEN
INSERT(id,
FakeVarchar,
FakeInt,
FakeDate)
VALUES
(Source.id,
Source.FakeVarchar,
Source.FakeInt,
Source.FakeDate
);
-- Records that should be deleted
DELETE dbo.TestRecords1Dest
WHERE ID IN
(
SELECT C.id
FROM CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C
WHERE SYS_CHANGE_OPERATION = 'D'
AND C.id NOT IN
(
SELECT t.id
FROM dbo.TestRecords1 t
LEFT JOIN CHANGETABLE(CHANGES dbo.TestRecords1, @bigint) AS C ON C.id = t.ID
WHERE SYS_CHANGE_VERSION >=
(
SELECT TOP 1 table_version
FROM dbo.VersionTracking
WHERE Table_Name = 'TestRecords1'
)
)
);
-- Set version to latest pull
UPDATE dbo.VersionTracking
SET
Table_LastVersion = (@bigint),
table_version = (@newbigint)
WHERE Table_Name = 'TestRecords1';
-- View result set aftewards.
-- TestRecords1Dest should contain three rows
Now we should see three rows in both tables as well as our Change Tracking versions matching.
/* (5) Scenario B: Test 2 - Change Tracking Current Version View current tracking version */ USE CTPOC; SELECT CHANGE_TRACKING_CURRENT_VERSION() as 'Change Tracking Current Version';
/* (6) Scenario B: Test 2 - Version Tracking Table View result set afterwards View Version Tracking table */ USE CTPOC; SELECT * FROM dbo.VersionTracking;
/* (7) Scenario B: Test 2 - TestRecords1 Table View TestRecords1 table */ USE CTPOC; SELECT * FROM dbo.TestRecords1;
/* (8) Scenario B: Test 2 - TestRecords1Dest Table View TestRecords1Dest table */ USE CTPOC; SELECT * FROM dbo.TestRecords1Dest;
Again our source matches our destination, even though we had deleted and reinserted the same identical row and processed an update statement on it. CT allows us to match the destination table to the source table regardless of the DML statements and how they are processed. To handle deletes you do need to add in additional logic vs when are you are only worrying about updating or inserting rows.
This solves all of our tests and scenarios for creating a basic proof of concept to start implementing Change Tracking. You can see my GitHub here for all of the code involved in this article.
If you have any questions or would like me to cover a feature or topic regarding Change Tracking, please let me know and I’d be happy to assist!