Change tracking

  • Synchronous
  • Available in Standard Edition since 2008
  • Old data -deletions, updates is not saved

Example:

SET NOCOUNT ON
go
PRINT ‘Creating test database’
Go
CREATE DATABASE Pruebas2
GO
USE pruebas2
go
PRINT ‘Get compatibility level of db’
GO

SELECT compatibility_level
FROM sys.databases WHERE name = ‘Pruebas2’;

GO
PRINT ‘Setting db isolation level’
ALTER DATABASE Pruebas2 SET ALLOW_SNAPSHOT_ISOLATION ON;

GO
PRINT ‘Creating ct_Tabl’
GO
CREATE TABLE dbo.ct_Tabl
(
Id INT NOT NULL ,
NAME VARCHAR(20)
NOT NULL CONSTRAINT [PK_ID_ct_Tabl] PRIMARY KEY CLUSTERED ( [Id] ASC )
);

GO
PRINT ‘Inserting initial values, before starting to track’
GO

INSERT INTO dbo.ct_Tabl
( Id, NAME )
VALUES ( 1, — Id – int
‘ABC-1’ — NAME – varchar(2)
),
( 2, ‘XXX-1’ );
GO

PRINT ‘See current change tracking version before Change tracking enabled’;

SELECT [change tracking version before Enabling] = CHANGE_TRACKING_CURRENT_VERSION();
GO
PRINT ‘Enable Change Tracking on database’;

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

GO
PRINT ‘Enable Change Tracking on testchange table’;
GO
ALTER TABLE [dbo].[ct_tabl]
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 ct_Tabl a
END
ELSE BEGIN
SELECT a.*
FROM ct_Tabl a INNER JOIN
CHANGETABLE(CHANGES dbo.ct_tabl, @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 A] = @lastVersion1

PRINT ‘insert new rows in table’

INSERT INTO dbo.ct_Tabl
( Id, NAME )
VALUES ( 3, — Id – int
‘YYY-2’ — NAME – varchar(2)
),
( 4, — Id – int
‘ZZZ-2’ — NAME – varchar(2)
)

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

PRINT ‘Get latest Version’
SELECT [Last Version B] = @lastVersion1

 

INSERT INTO dbo.ct_Tabl
( Id, NAME )
VALUES ( 5, — Id – int
‘KKK-3’ — NAME – varchar(2)
),
( 6, — Id – int
‘LLLL-3’ — NAME – varchar(2)
)

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

PRINT ‘Get latest Version’
SELECT [Last Version C] = @lastVersion1

UPDATE ct_Tabl set name = ‘Modified ZZZ’ where id = 4

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

PRINT ‘Get latest Version’
SELECT [Last Version D] = @lastVersion1

 

GO
PRINT ‘Disable Change Tracking on table’
ALTER TABLE dbo.ct_Tabl
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 Pruebas2 SET CHANGE_TRACKING = OFF

GO

PRINT ‘test complete, dropping database’
USE master
Go
DROP DATABASE Pruebas2