SQL Server 2008 Column Change Tracking

Article
02/06/2011

I could not find a good description of how SQL Server Change Tracking determines if a column changed so I decided to give it a try in 2008 R2 using the script below. Basically in the last update statement I update a column to the same value which already exists in the column. SQL appears to just record that the column was involved in the update statement. Unfortunately this means that anyone who desires to perform sync operations at the column level must write more complicated row update logic.

Before running the script manually create a database called ‘TrackingTest’.

CREATE TABLE [dbo].[Test](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [A] [varchar](50) NULL,
    [B] [int] NULL
CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

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

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

INSERT INTO dbo.Test (A,B) VALUES ('C', 50)
INSERT INTO dbo.Test (A,B) VALUES ('D', 50)

SELECT 'SYS_CHANGE_COLUMS = NULL means that all columns changed.'
SELECT
    CT.Id, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Test'), 'A', 'ColumnId'),CT.SYS_CHANGE_COLUMNS) AS A_CHANGED,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Test'), 'B', 'ColumnId'),CT.SYS_CHANGE_COLUMNS) AS B_CHANGED
FROM
    CHANGETABLE(CHANGES dbo.Test, NULL) AS CT

DECLARE @synchronization_version BIGINT
SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

SELECT * FROM dbo.Test
SELECT 'Changing both columns via "UPDATE dbo.Test SET A=''E'', B=51"'
UPDATE dbo.Test SET A='E', B=51
SELECT * FROM dbo.Test

SELECT
    CT.Id, CT.SYS_CHANGE_OPERATION,
    CT.SYS_CHANGE_COLUMNS, CT.SYS_CHANGE_CONTEXT,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Test'), 'A', 'ColumnId'),CT.SYS_CHANGE_COLUMNS) AS A_CHANGED,
    CHANGE_TRACKING_IS_COLUMN_IN_MASK (COLUMNPROPERTY(OBJECT_ID('dbo.Test'), 'B', 'ColumnId'),CT.SYS_CHANGE_COLUMNS) AS B_CHANGED
FROM
    CHANGETABLE(CHANGES dbo.Test, @synchronization_version) AS CT

SET @synchronization_version = CHANGE_TRACKING_CURRENT_VERSION();

SELECT * FROM dbo.Test
SELECT 'Only setting column A to the value already in the column via "UPDATE dbo.Test SET A=''E''"'
UPDATE dbo.Test SET A='E'
SELECT * FROM dbo.Test

SELECT 'Now SYS_CHANGE_COLUMS has a value showing that only A was changed'
SELECT 'To bad it isn''t smart enough to know that the data did not change!'

Results are:

SQL Server 2008 Column Change Tracking

Additional resources