Change Tracking

You may recall my last post on Change Data Control. This time I am going to talk about other option for tracking changes to tables on SQL Server: Change Tracking.

The main differences between the two are:

• Change Tracking works with SQL Server 2008 Express
• Change Tracking does not require SQL Server Agent to be running
• Change Tracking does not keep the old values in case of an UPDATE or DELETE
• Change Data Capture uses an asynchronous process, so there is no overhead on each operation
• Change Data Capture requires more storage and processing

Here's some code that illustrates it's usage:

-- for demonstrative purposes, table Post of database Blog only contains two columns, PostId and Title

-- enable change tracking for database Blog, for 2 days
ALTER DATABASE Blog
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

-- enable change tracking for table Post
ALTER TABLE Post
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

-- see current records on table Post
SELECT * FROM Post

SELECT * FROM sys.sysobjects WHERE name = 'Post'
SELECT * FROM sys.sysdatabases WHERE name = 'Blog'

-- confirm that table Post and database Blog are being change tracked
SELECT * FROM sys.change_tracking_tables
SELECT * FROM sys.change_tracking_databases

-- see current version for table Post
SELECT p.PostId,
	p.Title,
	c.SYS_CHANGE_VERSION,
	c.SYS_CHANGE_CONTEXT
FROM Post AS p
CROSS APPLY CHANGETABLE(VERSION Post, (PostId), (p.PostId)) AS c;

-- update post
UPDATE Post
SET Title = 'First Post Title Changed'
WHERE Title = 'First Post Title';

-- see current version for table Post
SELECT p.PostId,
	p.Title,
	c.SYS_CHANGE_VERSION,
	c.SYS_CHANGE_CONTEXT
FROM Post AS p
CROSS APPLY CHANGETABLE(VERSION Post, (PostId), (p.PostId)) AS c;

-- see changes since version 0 (initial)
SELECT p.Title, c.PostId, 
	SYS_CHANGE_VERSION,
	SYS_CHANGE_OPERATION,
	SYS_CHANGE_COLUMNS,
	SYS_CHANGE_CONTEXT 
FROM CHANGETABLE(CHANGES Post, 0) AS c
LEFT OUTER JOIN Post AS p
ON p.PostId = c.PostId;

-- is column Title of table Post changed since version 0?
SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(COLUMNPROPERTY(OBJECT_ID('Post'), 'Title', 'ColumnId'), (SELECT SYS_CHANGE_COLUMNS FROM CHANGETABLE(CHANGES Post, 0) AS c))	

-- get current version
SELECT CHANGE_TRACKING_CURRENT_VERSION()

-- disable change tracking for table Post
ALTER TABLE Post
DISABLE CHANGE_TRACKING;

-- disable change tracking for database Blog
ALTER DATABASE Blog
SET CHANGE_TRACKING = OFF;

You can read about the differences between the two options here. Choose the one that best suits your needs!