Change Tracking and Primary keys
2
votes
1
answer
278
views
I have a SQL Server database with Change Tracking enabled on a hand full of tables and it's used by another application on the cloud to sync data between both databases.
When an existing customer record in SQL Server needs to be synced to the cloud, for the first time, it's important that the order in which data is synced is done correctly as it would not make sense to sync the address data first without its pre-existing customer record (in the cloud), so I've written code to sync data for existing customers in a specific order so as to not cause sync errors at the destination cloud data app.
I don't know what columns within the eight tables need to be synced, so I've just been updating one column, the primary key and upon checking the system data captured by Change Tracking, I've noticed that ALL columns within a table are showing as "updated".
This is good because it has saved me a lot of scripting, I mean some of the tables are wide.
I assumed that this is the case because a clustered index PK is the data so updating the PK to the same value would cause all columns to update too.
Am I right to assume this?
Interestingly, if I update a the value to a PK that is NOT clustered, it has the same effect, although the table does have a clustered index but it's not the PK.
I was expecting one column, the PK to be the only column to be updated, but I am not complaining, just need to verify that updating the PK is a shortcut to scripting updates for all columns.
Asked by MindDData
(123 rep)
Jun 18, 2025, 06:43 AM
Last activity: Jun 18, 2025, 08:34 AM
Last activity: Jun 18, 2025, 08:34 AM