Enabling Change Tracking SQL

USE demostart
GO
CREATE DATABASE demostart
GO
USE demostart
GO
CREATE TABLE EmployeeMaster
(
EmployeeID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
CurrentPayScale DECIMAL
)
GO
INSERT INTO EmployeeMaster(FirstName, LastName, CurrentPayScale)
VALUES
('Saurabh', 'Singh', 10000),
('Ankit', 'Singh', 12000),
('Raj', 'Verma', 12000)
GO

-----------------------------------------------------
--Enabling Change Tracking at Database Level
ALTER DATABASE demostart
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)
--AUTO_CLEANUP -> With this option you can switch ON or OFF automatic
--tracking table clean up process
--CHANGE_RETENTION -> With this option, you can specify the time frame
--for which tracked information will be maintained
--Enabling Change Tracking at Table Level
ALTER TABLE EmployeeMaster
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)
--TRACK_COLUMNS_UPDATED -> With this option, you can include columns
--also whose values were changed


-----------------------------------------------------------

SELECT * FROM sys.change_tracking_databases
SELECT * FROM sys.change_tracking_tables
SELECT * FROM sys.internal_tables
WHERE parent_object_id = OBJECT_ID('EmployeeMaster')

-----------------------------------------------------------------
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('EmployeeMaster'))
SELECT * FROM CHANGETABLE
(CHANGES EmployeeMaster,0) as CT ORDER BY SYS_CHANGE_VERSION


-----------------------------------------------------------------

INSERT INTO EmployeeMaster(FirstName, LastName, CurrentPayScale)
VALUES('Anjali', 'Yadav', 10000)
GO
DELETE FROM EmployeeMaster
WHERE EmployeeID = 2
GO
UPDATE EmployeeMaster
SET CurrentPayScale = 15000, FirstName = 'Rana'
WHERE EmployeeID = 3
GO
SELECT CHANGE_TRACKING_CURRENT_VERSION ()
SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('EmployeeMaster'))
SELECT * FROM CHANGETABLE
(CHANGES Employee,0) as CT ORDER BY SYS_CHANGE_VERSION

SELECT * FROM CHANGETABLE
(CHANGES Employee,1) as CT ORDER BY SYS_CHANGE_VERSION
GO
-----------------------------------------------------
-- Get all DML changes (Inserts, Updates and Deletes) after the previous synchronized version
DECLARE @PreviousVersion bigint
SET @PreviousVersion = 1
SELECT CTTable.EmployeeID, CTTable.SYS_CHANGE_OPERATION,
Emp.FirstName, Emp.LastName, Emp.CurrentPayScale,
CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT
FROM CHANGETABLE (CHANGES EmployeeMaster, @PreviousVersion) AS CTTable
LEFT OUTER JOIN EmployeeMaster AS Emp
ON emp.EmployeeID = CTTable.EmployeeID
GO