3

We are running SQL 2008 R2 and have started exploring change tracking as our method for identifying changes to export to our data warehouse. We are only interested in specific columns.

We are identifying the changes on a replicated copy of the source database. If we query the change table on the source server, any specific column update is available and the SYS_CHANGE_COLUMNS is populated.

However on the replicated copy the changes are being tracked but the SYS_CHANGE_COLUMNS field is always NULL for an update change.

Track columns updated is set to true on the subscriber.

Is this due to the way replication works and it is performing whole row updates and therefore you cannot get column level changes on a subscriber?

Any help or alternative approaches would be much appreciated.

Thanks

Scott
  • 31
  • 2

1 Answers1

4

I realize this is an old question, but since I've happened across it I figure I may as well provide an answer for others who come later.

SYS_CHANGE_COLUMNS is null when every column is "updated". "Updated" here doesn't nessarily mean the value changed, it just means the column was touched by the DML statement. So, "update t set c = c" would mean column c was "updated".

Inserts and deletes will therefore always have a SYS_COLUMNS_CHANGED value of "null", since the whole row is affected by an insert or a delete. But most replication technologies do an update by setting every column value to the value of the column on the replication source. Therefore, a replication "update" will touch every column, and so the SYS_CHANGE_COLUMNS value will always be null.

allmhuran
  • 4,154
  • 1
  • 8
  • 27