Change Tracking in SQL Server

Change Tracking identifies the rows that have changed in the Table .

Most of the Things in SQL SERVER . We can do by Two ways — 1 . First Graphically 2. Second by Query.

Enable Change Tracking on Database .

By Graphically : Right click on Database . Select Property . click on “Change Tracking ” . Set the Database Properties—– 1. Change Tracking “True” 2. Retention Period 3. Retention Period Units 4. Auto Cleanup ” True ” .

2 . By Query .

-- To Enable Change Tracking by Query on Table
-- Dell1 is the Table Name. by Query.

ALTER TABLE Dell1
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

— To Enable Change Tracking by Graphically on Table . Right click on Table . Open Table Properties . Select ” Change Tracking ” . Make that ” True ” .

Let’s do Any Modification on Table like insert , update and delete and execute the following Query .

To find find out table version . execute following Query .

To Display which Database has Change Tracking Property Enable. Write below Query .

Retention Period The retention period for change tracking information. Change information will be retained for at least. The time period that is specified by the retention period and retention period units.

Change Tracking , Retention Period , Retention Period Units , Auto Cleanup

Change Tracking Indicates whether the table is enabled for change tracking. If the table is enabled , Information about changes to table data will be stored and can be used to determine Which rows have been changed.

Auto Cleanup Automatically cleanup the change tracking information by using the specified retention period. If off change tracking information will not be removed and will continue to grow.

Key Points

1. Without primary key on table . We cannot enable change tracking on table . Change tracking requires a primary key on the table . so we must create a primary key on the table before enabling change tracking.