SQL Server 2008 Column Change Tracking
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] GO 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); GO 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!’ 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 Results are:
Read more from the original source:
SQL Server 2008 Column Change Tracking
