SQL Server Change Tracking Example

It is not the same as logical replication in PostgreSQL but still good enough to track changes in SQL table

-- 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

So theoreticaly it might be something like infinite loop with backoff sleep timer tracking last processed version and processing new changes, aka sync to elastic, invalidate redis cache or producting kafka messages