change tracking: TRACK_COLUMNS_UPDATED not working?

Question

I have a table named tdc: create table tdc(pk int primary key,val int)

I am trying to use change tracking on it. Here's the script:

drop table tdc
create table tdc(pk int primary key,val int)

ALTER TABLE tdc
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

insert tdc(pk,val) select 0,432
insert tdc(pk,val) select 1,507
insert tdc(pk,val) select 2,312
insert tdc(pk,val) select 4,432 union all select 5,634
update tdc set val=888 where pk in(0,2)
update tdc set val=777 where pk in(0,2)

declare @from bigint= (select  CHANGE_TRACKING_MIN_VALID_VERSION(object_id('dbo.tdc')))
select * from CHANGETABLE(CHANGES tdc,@from)q

I get:

+====================+=============================+======================+====================+====================+====+
| SYS_CHANGE_VERSION | SYS_CHANGE_CREATION_VERSION | SYS_CHANGE_OPERATION | SYS_CHANGE_COLUMNS | SYS_CHANGE_CONTEXT | pk |
+====================+=============================+======================+====================+====================+====+
|                 49 |                          44 | I                    | NULL               | NULL               |  0 |
+--------------------+-----------------------------+----------------------+--------------------+--------------------+----+
|                 45 |                          45 | I                    | NULL               | NULL               |  1 |
+--------------------+-----------------------------+----------------------+--------------------+--------------------+----+
|                 49 |                          46 | I                    | NULL               | NULL               |  2 |
+--------------------+-----------------------------+----------------------+--------------------+--------------------+----+
|                 47 |                          47 | I                    | NULL               | NULL               |  4 |
+--------------------+-----------------------------+----------------------+--------------------+--------------------+----+
|                 47 |                          47 | I                    | NULL               | NULL               |  5 |
+--------------------+-----------------------------+----------------------+--------------------+--------------------+----+

Why is SYS_CHANGE_COLUMNS NULL for the update operation corresponding to SYS_CHANGE_VERSION 49?

As you can deduce from the SYS_CHANGE_VERSION, I have drop-created this table multiple times. It might be helpful to mention that I had also change data capture enabled in this table. Interestingly enough, cdc works correctly.

score 2 · Accepted Answer · answered May 13 '19 at 14:45

The results are actually correct!

First of all, Change Tracking and CDC are unrelated. CDC is an enterprise feature that captures every change, including original and modified data.

Change Tracking on the other hand is a lightweight feature available in all editions that returns the change state per row since a specific change tracking version. That's what makes it so cheap to use.

Per row is significant here because it means that only one record will be returned for each row, even if it was changed multiple times and eventually deleted. All changes made since the @last_sync_version we provide will be aggregated and a single record will be returned.

Consider, what is the state for row with PK 0 when you ask for all changes since the beginning of time, well, tracking? It's new, so its status is rightly I and there are no changed columns.

You'll get the same result if you search since the minimum version for that table. At this point in time, the change tracking table includes the I record, so that's is what's returned.

If you ask for changes since a newer version though, you'd get the U record :

declare @version bigint=CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID('dbo.tdc'));
declare @next bigint=@version+1
select * from CHANGETABLE(CHANGES tdc,@next)q

SYS_CHANGE_VERSION  SYS_CHANGE_CREATION_VERSION SYS_CHANGE_OPERATION    SYS_CHANGE_COLUMNS  SYS_CHANGE_CONTEXT  pk
20  NULL    U   0x0000000003000000  NULL    0
20  NULL    U   0x0000000003000000  NULL    2

This is quite enlightening (special thanks for the edition info). I understand now that I got "I" since the row was inserted since min_valid_version. But, when I add one to the version as per your code, although SYS_CHANGE_OPERATION indeed show as U, I still get SYS_CHANGE_COLUMNS null. Is there anything else I am missing on the flow? — George Menoutis, May 13 '19 at 16:20

change tracking: TRACK_COLUMNS_UPDATED not working?

1 Answers1