CHANGE TRACKING CAPTURE IN SQL SERVER 2008

Hi Frds,

This is one of the new feature introduced in SQL SERVER 2008.

Change tracking in sql server 2008 enables the applications to obtain only the changes made to the user tables along with the information of that particular changes. With change tracking integrated into sql server. In other words that it tracks the DML operations like(INSERT,DELETE AND UPDATE).

--Enabling the change tracking in Database level and table level

Alter database

Set change_tracking = ON(change_retention = 2 days,Auto_cleanup = ON)

Alter table

Enable change_tracking

With (track_columns_updated = ON)

Select * from sys.change_tracking_databases

Select * from sys.change_tracking_tables

Select * from sys.internal_tables where parent_object_id = OBEJCT_ID(‘tablename’)

Select CHANGE_TRACKING_CURRENT_VERSION()

Select CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(‘tablename’))

Select * from changetable(CHANGES tablename,0) as CT order by sys_change_version

DECLARE @PreviousVersion bigint

SET @PreviousVersion = 1

SELECT CTTable.EmployeeID, CTTable.SYS_CHANGE_OPERATION,

Emp.FirstName, Emp.LastName, Emp.CurrentPayScale,

CTTable.SYS_CHANGE_VERSION, CTTable.SYS_CHANGE_COLUMNS, CTTable.SYS_CHANGE_CONTEXT

FROM CHANGETABLE (CHANGES Employee, @PreviousVersion) AS CTTable

LEFT OUTER JOIN Employee AS Emp

ON emp.EmployeeID = CTTable.EmployeeID

GO

Hope you Guys enjoyed the subject.

Without having the fun in the subject we cant gain the knowledge. Agree? haa!