3

When a row is updated the SYS_CHANGE_OPERATION column is still showing I (Insert) and not U (Update) when querying CHANGETABLE .

Here's what I'm doing.

USE master
GO
CREATE DATABASE TestCT ; 
GO
SELECT compatibility_level , @@VERSION 
FROM sys.databases WHERE name = 'TestCT'; 

compatibility_level = 160
@@Version = Microsoft SQL Server 2022 (RTM) - 16.0.1000.6 (X64) Oct 8 2022 05:58:25 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 (Build 19045: ) (Hypervisor)

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

USE TestCT;
GO
DROP TABLE IF EXISTS dbo.TestCT
CREATE TABLE dbo.TestCT(
    Id      INT IDENTITY (1,1) CONSTRAINT PK__TestCT PRIMARY KEY ,
    Col1    VARCHAR(100)
) ; 
GO

ALTER TABLE dbo.TestCT
ENABLE CHANGE_TRACKING
WITH (TRACK_COLUMNS_UPDATED = ON) ; 
GO

INSERT INTO dbo.TestCT  (Col1)
            VALUES      ('INSERT1') ; 
GO
SELECT * 
FROM CHANGETABLE (CHANGES dbo.TestCT,0) as  CT 
LEFT JOIN dbo.TestCT         EM ON CT.Id = EM.Id
ORDER BY SYS_CHANGE_VERSION ; 
GO

enter image description here

SYS_CHANGE_OPERATION here is I (Insert) as expected.

Now if I update the row

UPDATE  T 
SET     Col1 = 'UPDATE'
FROM    dbo.TestCT   T 
WHERE   Col1 = 'INSERT1';
GO

enter image description here

SYS_CHANGE_OPERATION here is still I (Insert) and not U (Update). As you can see SYS_CHANGE_VERSION has incremented by 1 as expected.

What am I doing wrong here?

1 Answer 1

4

You aren't doing anything wrong, except ;-), in this case, the result should be an INSERT. This is because in your query, you are requesting everything with the start of 0.

This means, you will get the definition from 0. Since 0, there has been an INSERT and an update, however when you will see the data, it will have the latest result. Therefor the UPDATE is not relevant. Since 0 it has been an INSERT for the reader. When you change the 0 in a 1, SQL will see the changes since 1, and then it will be an UPDATE.

Microsoft has some good documentation about this: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/work-with-change-tracking-sql-server?view=sql-server-ver16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.