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
Enable Change Tracking on database level
ALTER DATABASE [PL] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 30 MINUTES); GO
Create test tables
-- 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 test data
-- 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
Check sys.change_tracking_databases
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 :-)