SYS_CHANGE_OPERATION not showing correct value when row is Updated in CHANGE TRACKING

3 votes

1 answer

719 views

                          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



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



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?


                        

Asked by Geezer (513 rep)

Dec 4, 2022, 12:28 PM
Last activity: Dec 4, 2022, 01:18 PM

SYS_CHANGE_OPERATION not showing correct value when row is Updated in CHANGE TRACKING

Related Questions