Change Tracking

Change tracking keeps logs of events that cause data to change in the database. Tracking can be enabled on a per-table basis. A change tracking table is created for each table that is being tracked. The change tracking table stores:

1. Primary key
2. Version number for the creation of the row
3. Version number for the last change to the row
4. Type of change operation executed
5. Columns changed by the operation

Change tracking must be enable first at the database level, then for each required table:

ALTER DATABASE db
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 5, AUTO_CLEANUP = ON)
GO

ALTER TABLE table 
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON)

With change tracking enabled, view changes with the following commands:

SELECT * FROM CHANGETABLE(CHANGES, table, 0)

SELECT CHANGE_TRACKING_CURRENT_VERSION()

To turn off change tracking use:

ALTER TABLE table
DISABLE CHANGE_TRACKING

ALTER DATABASE db
SET CHANGE_TRACKING = OFF

Change Data Capture

Change Data Capture records the data that has been changed and requires SQL Server Agent to be running on the instance. CDC is enabled via two stored procedures:

USE DATABASE db

sys.sp_cdc_enable_db

sys.sp_cdc_enable_table

CDC captures data via an asynchronous process based on the transaction log and should not therefore have significant impact on the performance of processes making changes to tables. CDC captures:

1. LSN - last sequence number
2. DML operation
3. bitmask representing modified columns
4. columns from original source table