Change Tracking example -Sql Server

If there is a requirement to get incremental or changed data from database frequently without putting a heavy load on database objects, then Change Tracking mechanism of Sql Server can be out of the box solution for this requirement. Normally, developers have to do custom implementation to achieve change tracking behavior. It can be implementation by considering triggers, timestamp columns, or maintaining new tables.

Following is step by step instructions to enable and use of change tracking feature in SQL Server.

Step 1: Check if database compatibility level is set to 90 or greater. If It is lower than 90 then change tracking will not work.

SELECT compatibility_level
FROM sys.databases WHERE name = '';


Step 2: Enable Isolation level on a database to Snapshot. It will ensure change tracking information is consistent.

ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON

Step 3: Set Change tracking on a database.

ALTER DATABASE SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

CHANGE_RETENTION: It specifies the time period for which change tracking information is kept
AUTO_CLEANUP: It enables or disables the cleanup task that removes old change tracking information.

Step 4: Enable change tracking on a table.

ALTER TABLE
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF)

TRACK_COLUMNS_UPDATED: Setting value to “ON” will make SQL Server Engine storing extra information about columns which are enabled for change tracking. ‘OFF’ is default value to avoid extra overhead on SQL Server to maintain extra columns information.

Step 5: Example to get changed data.

It is example of SQL procedure which will only send changed data from table. Application can pass @lastVersion = 0 first time and going forward application can keep the last version in the cache and pass on last stored version.


CREATE PROCEDURE [dbo].[GetIncrementalChanges]
@lastVersion BIGINT = 0 OUTPUT
AS
BEGIN
DECLARE @curVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION()
IF @lastVersion = 0
BEGIN
SELECT
a.*
FROM a
END
ELSE
BEGIN
SELECT
a.*
FROM a
INNER JOIN CHANGETABLE(CHANGES , @lastVersion) ct ON A.Id= ct.Id
END

SET @lastVersion = @curVersion

END


Disable Change Tracking

1. Before disabling change tracking on a database, all tables should have change tracking disabled.

Testing Sql statements

You can find working example in attached SQL file or code below:

changetracking


SET NOCOUNT ON
go
PRINT 'Creating test database'
Go
CREATE DATABASE testDb
GO
USE testDb
go
PRINT 'Get compatibility level of db'
GO

SELECT compatibility_level
FROM sys.databases WHERE name = 'v';

GO
PRINT 'Setting db isolation level'
ALTER DATABASE testDb SET ALLOW_SNAPSHOT_ISOLATION ON;

GO
PRINT 'Creating table testchange'
GO
CREATE TABLE dbo.TestChange
(
Id INT NOT NULL ,
NAME VARCHAR(20)
NOT NULL CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED ( [Id] ASC )
);

GO
PRINT 'Inserting initial values'
GO

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 1, -- Id - int
'ABC' -- NAME - varchar(2)
),
( 2, 'XXX' );
GO

PRINT 'See current change tracking version before Change tracking enabled';

SELECT [change tracking version after Enabling] = CHANGE_TRACKING_CURRENT_VERSION();
GO
PRINT 'Enable Change Tracking on database';

ALTER DATABASE testDb SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS,AUTO_CLEANUP = ON)

GO
PRINT 'Enable Change Tracking on testchange table';
GO
ALTER TABLE dbo.TestChange
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = OFF);

GO

SELECT [change tracking version after Enabling] = CHANGE_TRACKING_CURRENT_VERSION();

GO
CREATE PROCEDURE [dbo].[GetIncrementalChanges]
@lastVersion BIGINT = 0 OUTPUT
AS
BEGIN
DECLARE @curVersion BIGINT = CHANGE_TRACKING_CURRENT_VERSION()
IF @lastVersion = 0
BEGIN
SELECT
a.*
FROM TestChange a
END
ELSE
BEGIN
SELECT
a.*
FROM TestChange a
INNER JOIN CHANGETABLE(CHANGES dbo.TestChange, @lastVersion) ct ON A.Id= ct.Id
END

SET @lastVersion = @curVersion

END
GO

DECLARE @lastVersion1 BIGINT =0

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get Last Version'
SELECT [Last Version] = @lastVersion1

PRINT 'insert new rows in table'

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 3, -- Id - int
'YYYY' -- NAME - varchar(2)
),
( 4, -- Id - int
'ZZZ' -- NAME - varchar(2)
)

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get latest Version'
SELECT @lastVersion1

INSERT INTO dbo.TestChange
( Id, NAME )
VALUES ( 5, -- Id - int
'KKKK' -- NAME - varchar(2)
),
( 6, -- Id - int
'LLLL' -- NAME - varchar(2)
)

EXECUTE dbo.GetIncrementalChanges @lastVersion = @lastVersion1 OUTPUT -- bigint

PRINT 'Get latest Version'
SELECT @lastVersion1

GO
PRINT 'Disable Change Tracking on table'
ALTER TABLE dbo.TestChange
DISABLE CHANGE_TRACKING
GO
PRINT 'Current change tracking version after disabling';
SELECT [change tracking version after disabling] = CHANGE_TRACKING_CURRENT_VERSION()
GO
PRINT 'Disable Change Tracking on Database'

ALTER DATABASE testDb SET CHANGE_TRACKING = OFF

GO

PRINT 'test complete, dropping database'
USE master
Go
DROP DATABASE testDb