In the last posts we played with Change Tracking (CT):

T-SQL: Change Tracking – What is this and how it works
T-SQL: Change Tracking Retention Period – How Long the Changes are Kept?

Let’s execute few queries and see what do we have after the retention period expires.

Create database

USE [master];
GO

IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO

CREATE DATABASE PL;
GO

USE PL;
GO

ALTER DATABASE [PL]
	SET CHANGE_TRACKING = ON
	(CHANGE_RETENTION = 30 MINUTES);
GO

-- No Change Tracking on columns
CREATE TABLE dbo.ChangeTrackingYes
	(
		ChangeTrackingYesID INT IDENTITY(1, 1) PRIMARY KEY
		, [Value] NVARCHAR(128)
	);
GO

ALTER TABLE dbo.ChangeTrackingYes
	ENABLE CHANGE_TRACKING;
GO

-- dbo.ChangeTrackingColumnsYes tracks columns updates
CREATE TABLE dbo.ChangeTrackingColumnsYes
	(
		ChangeTrackingColumnsYesID INT IDENTITY(1, 1) PRIMARY KEY
		, Value1 NVARCHAR(128)
		, [Value2] NVARCHAR(128)
	);
GO

ALTER TABLE dbo.ChangeTrackingColumnsYes
	ENABLE CHANGE_TRACKING
	WITH (TRACK_COLUMNS_UPDATED = ON);
GO

-- Insert into tracked table
INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'AI';
INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'BI';
INSERT dbo.ChangeTrackingYes ([Value]) SELECT 'CI';
GO

-- Insert into tracked columns table
INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'AI', 'XI';
INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'BI', 'YI';
INSERT dbo.ChangeTrackingColumnsYes (Value1, Value2) SELECT 'CI', 'ZI';
GO

SELECT *
FROM sys.change_tracking_databases;
GO

In sys.internal_tables, we can see that the CT tables are created on 2017-12-12 @ 11:53 AM

SELECT
	OBJECT_SCHEMA_NAME([object_id]) AS SchemaName
	, '<--->' AS ' '
	, *
FROM sys.internal_tables
WHERE
	parent_object_id IN
	(
		SELECT [object_id]
		FROM sys.change_tracking_tables
	);
GO

The current CT Version is

SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

Check CHANGETABLE(CHANGES…) on 2017-12-12 @ 11:54 AM

SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT;
GO

Run the same query on 2017-12-13 @ 9:12 AM

SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT;
GO

The retention period has expired and we can’t track the changes with CT version less than or equal to 6. In a scenario in which we use CT for ETL purposes, we need to run the ETL more often than the retention period.

Update some values and check the current CT version

-- Update dbo.ChangeTrackingYes
UPDATE dbo.ChangeTrackingYes
SET [Value] = 'BU'
WHERE ChangeTrackingYesID = 2;

-- Current CT Version
SELECT CHANGE_TRACKING_CURRENT_VERSION() AS CTCurrVersion;
GO

With the built-in function CHANGE_TRACKING_MIN_VALID_VERSION() we can determine the CT version that is reasonable to use after the retention period expires. It shows the version AFTER which the changes are kept in the database.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.ChangeTrackingYes')) AS CTMinValidVersion;
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.ChangeTrackingColumnsYes')) AS CTMinValidVersion;
GO

Lets compare the changes after version 0 (all the changes) and version 6

-- Check CT Version = 0
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 0) AS CT;
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 0) AS CT;
GO

…and the changes after version 6

SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingYes, 6) AS CT;
SELECT CT.*, '<--->', 0 AS [Version], GETDATE() AS [Now] FROM CHANGETABLE(CHANGES dbo.ChangeTrackingColumnsYes, 6) AS CT;
GO

The data about the changes is lost, so we can’t track changes before version 7 anymore.

Keep it simple :-)