Configuring Change Tracking in Tables
After configuring Change Tracking on the
Server the next task is to enable it for the table.
Tacking is done by making a note of the
primary key of the row that changed along with a version number (NB change
tracking on a table requires it to have a primary key). Once a database is
enabled for change tracking a version number is instituted, which allows
ordering of operations. You can also optionally track which columns changed.
ALTER TABLE
[dbo].[tblName]
ENABLE CHANGE_TRACKING
WITH(TRACK_COLUMNS_UPDATED = ON)
You can get the information about tracking
tables by querying SELECT
* FROM sys.change_tracking_tables
To disable Change Tracking for a table use
the following T-SQL command.
ALTER TABLE
[dbo].[tblName]
DISABLE CHANGE_TRACKING
Enable Change Tracking for a table using SSMS
by selecting table Properties > Change Tracking , as shown in the below
screenshot:
Implementation Change Tracking
After a DML operation is made on database,
first the relevant operation will be performed on the table and then a row is
added to an internal change tracking table. There is one internal table for
each user table that uses change tracking.
Thus, change tracking is based on logging
information in data tables to capture what is happening.
Let us first create a table with multiple
columns with a primary key in a database in which change tracking is enabled.
CREATE TABLE
[dbo].[tblEmployees](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](30) NULL,
[Salary] [numeric](7, 2) NULL,
[Location] [varchar](5) NULL,
CONSTRAINT
[PK_tblEmployees] PRIMARY KEY CLUSTERED
(
[ID] ASC
)
) ON
[PRIMARY]
Then enable the change tracking on the tblEmployees
table with Column update on.
ALTER TABLE
[dbo].[tblEmployees]
ENABLE CHANGE_TRACKING
WITH(TRACK_COLUMNS_UPDATED = ON)
Next we will perform some sample
operations, first – Insert.
INSERT INTO dbo.tblEmployees
(Name,Salary,Location)
VALUES
(‘Dinesh’,10000,’SRL’),
(‘Asanka’,15000,’UK’),
(‘John’,17500,’UK’)
The above statement will insert three
records. (NB: This is a new feature in SQL Server 2008 where you can insert
multiple records using single INSERT statement.)
There is a change tracking function to
retrieve changed data called CHANGETABLE.
SELECT * FROM CHANGETABLE
(CHANGES dbo.tblEmployees,0) as CT
The CHANGETABLE function has two
parameters. The first parameter is to supply the table name. CHANGES is a key
word you have to include to retrieve the changes. Another option VERSION will
be discussed later.
The second parameter is the version number.
The version number will be maintained across the database. For a single
operation on any table which has Change Tracking enabled the version number
will be incremented. For example, after enabling Change Tracking, the version
number will be 0 and after inserting records in table A the version number will
be 1 and performing any updates will result in a version number of 2.
If you pass 0 for a version parameter, you
will get all the changes that were done after the 0 version which means all the
changes after the enabling the Change Tracking. The important thing to note
here is that you will get the net changes, which is very handy for data
warehousing.
The below is the output you receive from
running the above query.
SYS_CHANGE_VERSION: Version value that is
associated with the last change to the row
SYS_CHANGE_CREATION_VERSION: Version values
that are associated with the last insert operation.
SYS_CHANGE_OPERATION: Specifies the type of
change, U = Update, I = Insert, D = Delete
SYS_CHANGE_COLUMNS: Lists the columns that
have changed since the baseline version. This column has a value only for
Update operation. Also when all columns are updates, still this column will be
null.
SYS_CHANGE_CONTEXT: Change context
information that you can optionally specify by using the WITH clause as part of
an INSERT, UPDATE, or DELETE statement.
ID : The primary key of the base table
which will allow you to join Change table with the base table.
After doing some operations on the
tblEmployees and then running the below T-SQL
SELECT * FROM CHANGETABLE
(CHANGES dbo.tblEmployees,0) as CT
The following output will be generated:
You can see that though you have done
several changes, you will receive only the net changes. Hence only the Inserts
and deletes for the version 0 are shown.
SELECT * FROM CHANGETABLE
(CHANGES dbo.tblEmployees,2) as CT query will give you
following results.
There are a few things to note here. The second
result set has only eight records while first has 10 records. This is because
Id 4 and 5 records were not updated after version 2. Also you can see that
SYS_CHANGE_COLUMNS has some values and SYS_CHANGE_OPERATION values have changed
to U.
Update the SYS_CHANGE_CONTEXT Column
As before, there is a column called
SYS_CHANGE_CONTEXT and following example shows how to update that column so
it can be used as a audit feature.
DECLARE @originator_id varbinary(128);
SET @originator_id = CAST(‘Sample Application’ AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT
(@originator_id)
UPDATE
tblEmployees
SET Salary = 50000
WHERE ID = 1
Get All Rows With Latest Versions
The below query will return all the
columns and their latest versions. You can still use Inner join tblEmployees
with CHNAGETABLE (CHANGES dbo.tblEmployees, 0) but this query is better when
there are a large number of records.
SELECT emp.[ID],
emp.Name,
emp.Salary,
emp.Location,
ct.SYS_CHANGE_VERSION,
ct.SYS_CHANGE_CONTEXT
FROM dbo.tblemployees
AS emp
CROSS APPLY
CHANGETABLE
(VERSION dbo.tblEmployees, ([ID]), (emp.[ID])) AS ct;
