Implementing New Manageability Features and Practices - Microsoft SQL Server 2008 R2 Administration - page 277

CT is a light-weight synchronous mechanism that tracks data modifications but records only
the fact that the row has been changed, like writing leaf level information of modified data. CT
is very useful to represent a consistent view of modified data and detect data conflicts quickly.

In this recipe, we will look at how to design a change tracking methodology that uses CT
functionality to detect DML operations.

Getting ready

The underlying SQL Server instance level must be SQL Server 2008 R2 and the database
compatibility level must be 100 to design the change tracking feature.

To show the CT functionality, let us create a new database called PWD_WEF with default values
using the TSQL statement or the SSMS tool. This will create a new table that will be used to
demonstrate the CT functionality:

USE [PWD_WEF]
GO

CREATE TABLE [dbo].[WEF_Passwords](
[per_id] [numeric](8, 0) NOT NULL IDENTITY(1,1) PRIMARY KEY
CLUSTERED,
[fullname] [varchar](71) NULL,
[per_web_login] [varchar](50) NULL,
[per_web_password] [varchar](10) NULL
) ON [PRIMARY]

GO

In order to enable the change tracking on a table, the underlying table requires a primary key
on the table.

How to do it...

The following steps are required to design the CT functionality on an existing SQL Server
instance and database.

1. To use Change Tracking, we must first enable it for the database and then enable it at
the table level.

2. CT can be enabled through TSQL statements or through the SSMS tool.