Trying to understand some SQL Server change tracking features

Question

I'm working on SQL Server 2016 SP1 with the Change Tracking feature and I have a question for you.

I have a database which has Change Tracking enabled. That database contains a table Table which has "change tracking" activated, but not "track columns updated" option.

For the example, on Table, I only have one column called Id of type is "uniqueidentifier", which is my PK.

On start, my change tracking current version is 0.

I got it with :

SELECT CHANGE_TRACKING_CURRENT_VERSION();

I added a new row to Table:

INSERT INTO dbo.[Table] (Id) 
VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');

Now, my change tracking current version is now 1.

With this request, I can see my element in the change tracking system :

SELECT * 
FROM CHANGETABLE (CHANGES dbo.[Table], 0) CT;

The result is :

Now, I delete my row with this :

DELETE FROM dbo.[Table] 
WHERE Id = 'C99F9E2A-1974-47CE-A406-481076F53BBD';

Change tracking current version is now 2.

I insert it again with the same request than previous.

Change tracking current version is now 3.

With this request, I got this result :

SELECT * 
FROM CHANGETABLE (CHANGES dbo.[Table], 1) CT;

Now is my question, why I got "U" in SYS_CHANGE_OPERATION ?

Why not "I" cause 1 < SYS_CHANGE_CREATION_VERSION which is 3 ?

Thanks for your help !

Answer 1

Adding some motivation, the intent of the change tracking functions is to enable you to see what has changed since the last time you checked, and enable you to harvest and apply those changes to some other table or external system.

If you look at the changes since just after the delete you will see the operation as an I. But if you look at the change since before the delete, you "skip over" the delete. But change tracking doesn't remember what the non-key values were before the delete. So the row is reported as having been updated.

EG

ALTER DATABASE current  
SET CHANGE_TRACKING = ON  
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON)  
go
drop table if exists ct

create table ct(id uniqueidentifier primary key)

ALTER TABLE ct
ENABLE CHANGE_TRACKING  
WITH (TRACK_COLUMNS_UPDATED = ON) 

declare @beforeInsert bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
INSERT INTO dbo.ct (Id) VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');
declare @afterInsert bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
DELETE FROM dbo.ct WHERE Id = 'C99F9E2A-1974-47CE-A406-481076F53BBD';
declare @afterDelete bigint = (SELECT CHANGE_TRACKING_CURRENT_VERSION());
INSERT INTO dbo.ct (Id) VALUES ('C99F9E2A-1974-47CE-A406-481076F53BBD');


SELECT 'from before insert to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @beforeInsert) CT
union all
SELECT 'from after insert to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @afterInsert) CT
union all
SELECT 'from after delete to current',id, sys_change_operation FROM CHANGETABLE (CHANGES dbo.ct, @afterDelete) CT

outputs

                              id                                   sys_change_operation
----------------------------- ------------------------------------ --------------------
from before insert to current C99F9E2A-1974-47CE-A406-481076F53BBD I
from after insert to current  C99F9E2A-1974-47CE-A406-481076F53BBD U
from after delete to current  C99F9E2A-1974-47CE-A406-481076F53BBD I

Answer 2

From Microsoft Docs CHANGETABLE

If you delete a row and then insert a row that has the old primary key, the change is seen as an update to all columns in the row.

Presumably, id is the primary key of the table in question.

Also, don't use id as the name of a column, use TableID... so if the table name is Users, then the primary key (if using a surrogate key) should be UserID. Using id as the name of your primary key column makes for considerable confusion and makes your code error-prone. Take a look at this answer for details about that.