Change Tracking
- by Ricardo Peres
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!
SyntaxHighlighter.config.clipboardSwf = 'http://alexgorbatchev.com/pub/sh/2.0.320/scripts/clipboard.swf';
SyntaxHighlighter.brushes.CSharp.aliases = ['c#', 'c-sharp', 'csharp'];
SyntaxHighlighter.brushes.Xml.aliases = ['xml'];
SyntaxHighlighter.all();
