Link to home
Start Free TrialLog in
Avatar of Member_2_7966113
Member_2_7966113

asked on

SQL Change Tracking Issue pt3

Experts I'm going resubmit a too question on Change Tracking with fresh data


The table structure I'm working with is as follows:

CREATE TABLE PrestigeCars.dbo.data_source_table (
  PersonID INT NOT NULL
 ,Name VARCHAR(255) NULL
 ,Age INT NULL
 ,PRIMARY KEY CLUSTERED (PersonID)
) ON [PRIMARY]
GO

Open in new window


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

Open in new window



Before doing any modifications to the dataset it looks as follows

User generated image
The actual dataset is as follows:

create table data_source_table
(
    PersonID int NOT NULL,
    Name varchar(255),
    Age int
    PRIMARY KEY (PersonID)
);

INSERT INTO data_source_table
    (PersonID, Name, Age)
VALUES
    (1, 'aaaa', 21),
    (2, 'bbbb', 24),
    (3, 'cccc', 20),
    (4, 'dddd', 26),
    (5, 'eeee', 22);

Open in new window



Now, I have modified the dataset as follows:

User generated image
From the above you can see I have inserted a row 'InsertedCol', I have updated a row at PersonID 5 'eeee-updatedCol', and I have deleted a row at PersonID 4 'dddd'.

User generated image
I have then executed the following code:

 
SELECT
    ChVer = SYS_CHANGE_VERSION,
    ChCrVer = SYS_CHANGE_CREATION_VERSION,
    ChOp = SYS_CHANGE_OPERATION, PA.*
  FROM CHANGETABLE(CHANGES dbo.data_source_table, 29) AS ChTbl
 LEFT JOIN dbo.data_source_table PA ON PA.PersonID = ChTbl.PersonID
And the results are as follows:

Open in new window




You can see everything is working fine, but where it should say 'dddd' in the name column is gives the results (null).

I need 'dddd' where the null is.

Can you help me with that?
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

In change tracking CHANGETABLE you will only see the Key (PersonID). This means that when you LEFT JOIN on the main table, the PersonID of 4 will not be there so you will only see NULLs.

Everything is working as designed.  You will only see those that have been inserted or updated, but not deleted because the source table doesn't have the key.
ASKER CERTIFIED SOLUTION
Avatar of DBAduck - Ben Miller
DBAduck - Ben Miller
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Member_2_7966113
Member_2_7966113

ASKER

Ben you really nailed it for me. I was struggling to comprehend this, but your answer made it very clear.

I just have one thought. I got the original idea from the following link https://solutioncenter.apexsql.com/reading-sql-server-change-tracking-results/

Can you explain how they were able to see deleted rows?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial