SQL Server Change Tracking Example

-- DROP TABLE Demo2;
CREATE TABLE Demo2 (Id INT NOT NULL PRIMARY KEY, Name NVARCHAR(200) NOT NULL);
INSERT INTO Demo2(Id, Name) VALUES (1, 'one'), (2, 'two');
SELECT Id, Name FROM Demo2;

-- prerequisites
ALTER DATABASE RabotaUA2 SET ALLOW_SNAPSHOT_ISOLATION ON;
ALTER DATABASE RabotaUA2 SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);
-- enable change tracking for concrete table
ALTER TABLE [RabotaUA2].[dbo].[Demo2] ENABLE CHANGE_TRACKING;

-- will be empty, because we enabled change tracking after adding records
SELECT SYS_CHANGE_VERSION AS V, SYS_CHANGE_OPERATION Op, Id FROM CHANGETABLE (CHANGES dbo.Demo2, 0) CT ORDER BY SYS_CHANGE_VERSION ASC;

-- demos
INSERT INTO Demo2(Id, Name) VALUES (3, 'three');
UPDATE Demo2 SET Name = 'two' WHERE Id = 2;
DELETE FROM Demo2 WHERE Id = 1;

-- now we will have desired changes
SELECT SYS_CHANGE_VERSION AS V, SYS_CHANGE_OPERATION Op, Id FROM CHANGETABLE (CHANGES dbo.Demo2, 0) CT ORDER BY SYS_CHANGE_VERSION ASC;
-- V	Op	Id
-- 16	I	3
-- 17	U	2
-- 18	D	1

-- we may start from last V we have processed, aka
SELECT SYS_CHANGE_VERSION AS V, SYS_CHANGE_OPERATION Op, Id FROM CHANGETABLE (CHANGES dbo.Demo2, 17) CT ORDER BY SYS_CHANGE_VERSION ASC;
-- V	Op	Id
-- 18	D	1

-- optionally we may join original data
SELECT SYS_CHANGE_VERSION AS V, SYS_CHANGE_OPERATION Op, T.* FROM CHANGETABLE (CHANGES dbo.Demo2, 0) CT LEFT JOIN dbo.Demo2 T ON CT.Id = T.Id ORDER BY SYS_CHANGE_VERSION ASC;
-- V	Op	Id		Name
-- 16	I	3		three
-- 17	U	2		two
-- 18	D	NULL	NULL