Change Tracking

First we need to enabled change tracking on the database.  This can be done using T-SQL or in SSMS.

ALTER DATABASE SimonBlog
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON);

ChangeTracking1

Now we can create a test table to track our changes against.

CREATE TABLE dbo.ChangeTrackingTest
(
 Id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
 SomeData VARCHAR(100) NOT NULL
);

Then we also need to enable change tracking on the table.  Again this can be done with either T-SQL or SSMS.

ALTER TABLE dbo.ChangeTrackingTest
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON);

ChangeTracking2

There are some change tracking functions that we can use to check the changes.  Firstly we can run the following to get the current version of the tracked database.

SELECT CHANGE_TRACKING_CURRENT_VERSION()

As we’ve not made any updates to the database this will return 0.  If change tracking was not enabled this would return NULL.

Now let’s insert some data

INSERT INTO dbo.ChangeTrackingTest (SomeData)
VALUES ('Simon'),('Peacock');

We’ve now got two rows in the table.  If we check the version against we get 1 returned.  Now we can use another function to view what changes have occurred since the last version.  We use the CHANGETABLE function as shown below.  The second parameter with a value of 0 tells the function to return the changes made since version 0.  This seems to be contrary to what BOL say.  Their examples pass the current version into the function.  If you do this you get no rows returned.  We’re currently at version 1 so if we pass in version 0 it will return the change made between version 0 and the current version.

SELECT * 
FROM CHANGETABLE(CHANGES dbo.ChangeTrackingTest, 0) AS CT

This gives us the following results.

ChangeTracking3

This show our two inserts for Ids 1 and 2.  The SYS_CHANGE_VERSION and SYS_CHANGE_CREATION_VERSION show that these changes were made as part of version 1.  The SYS_CHANGE_OPERATION value “I” shows that these were inserts.

Now we add a third row.

INSERT INTO dbo.ChangeTrackingTest (SomeData)
VALUES ('Eleanor')

The version number has increased to 2.  We now check CHANGETABLE for the changes made since version 1

SELECT * 
FROM CHANGETABLE(CHANGES dbo.ChangeTrackingTest, 1) AS CT

ChangeTracking4

This shows the new row with Id of 3 and that is was inserted as part of version 2.

We can also check for all changes since version 0, i.e. all the changes so far by passing in 0 as follows.

SELECT * 
FROM CHANGETABLE(CHANGES dbo.ChangeTrackingTest, 0) AS CT

This returns the following which shows all three inserts.

ChangeTracking5

Now let’s update a row.

UPDATE dbo.ChangeTrackingTest
SET SomeData = 'Nomis'
WHERE Id = 1

The version is now up to 3.  Let check for all changes since the beginning (version 0).

ChangeTracking6

You can see that the last time the row with Id = 1 was updated in version 3.  The creation version is still version 1.  When I first saw these results I was quite surprised that the SYS_CHANGE_OPERATION value still had “I” for insert.  However, if you just check for changes since the last version, i.e. since version 2 we get the following results.

ChangeTracking7

This shows us the update made in version 3.

You can view the current version of a particular row by using the VERSION command within the CHANGETABLE function as follows:

SELECT * 
FROM CHANGETABLE(VERSION dbo.ChangeTrackingTest, (Id), (1)) AS CT

As before the first parameter is the table name, the second is the name of the primary key column and the third is the value from the primary key column.  So this statement is asking for the current version of Id = 1 in dbo.ChangeTrackingTest and returns the following results as you would expect.

ChangeTracking8

The function CHANGE_TRACKING_MIN_VALID_VERSION returns the minimum valid version that we should use for tracking.

SELECT CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.ChangeTrackingTest'));

This returns a value of 1 and if we use that in the CHANGETABLE function we can see only the insert of row 3 and the update to row 1.

You may want to filter changes on the context in which they were made. You may have changes that are made by an application and you may have changes that are made by your DBA. You can use the WITH CHANGE_TRACKING_CONTEXT function when you make the change as in the following example. NB. the parameter supplied is a VARBINARY(128) data type.

DECLARE @originator_id VARBINARY(128);
SET @originator_id = CAST('SimonDBA' AS VARBINARY(128));
WITH CHANGE_TRACKING_CONTEXT (@originator_id)
	INSERT INTO dbo.ChangeTrackingTest (SomeData)
	VALUES ('Simon');

If we now run the following call to CHANGETABLE to view the changes since the last version we get the following results.

SELECT *, CAST(SYS_CHANGE_CONTEXT AS VARCHAR(100)) 
FROM CHANGETABLE(CHANGES dbo.ChangeTrackingTest, 3) AS CT

ChangeTracking9

The context value supplied is held in the SYS_CHANGE_CONTEXT column and can be cast back to the VARCHAR to make it human readable.

Advertisements

One thought on “Change Tracking

  1. Pingback: Exam 70-464 | Simon Learning SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s