Change tracking, sys_column_updated and context

Question

I am updating a name in a dbo.test table where ID = 1 with change_tracking_context=something. The purpose of using a context is to ignore the changes occurred in that context including column that it touched. Suppose some other updates happen to Status column in a same record before the synchronization.

Now, I am asking changes occurred since my last synchronized change version and use filter saying where sys_change_context is null. i expect my result should not include the name column since it was updated in a change tracking context. The result anyhow shows both the columns (name and status) even when the filter is used.

Is this a right behavior? Why it doesn't eliminate the column which is outside of the filter? What is a work around to eliminate the column change in specific context within change tracking? 

Any suggestion would bring more light on the subject.

Thanks,

---

DBA

Answer 1

Would it be possible for you to compose a repro that demonstrates the
behaviour?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

Sure, Here is the example. 

ALTER DATABASE <databaseName> set change_tracking=on ;
GO

 -- Function to return xml output of updated columns in change tracking.
 
      
      create function dbo.ChangeColumns
       (
         @tableSchema sysname
        ,@tableName sysname
        ,@changeColumns varbinary(4100)
       )
       returns xml
       as
       begin
       
        
         RETURN (SELECT
          C.COLUMN_NAME
         from INFORMATION_SCHEMA.COLUMNS c
         WHERE c.TABLE_NAME = @tableName
         and c.TABLE_SCHEMA = @tableSchema
         and CHANGE_TRACKING_IS_COLUMN_IN_MASK(c.ORDINAL_POSITION,@changeColumns) = 1
         for xml raw('changedColumns'),root ('root'));
         
         
         
       
       end      

GO
-- Test table with change tracking enabled

CREATE TABLE [dbo].[ChangeTrackingTest]
(
  ID int identity(1,1) constraint PK_ChangeTrackingTest_ID primary key
 ,name varchar(10) not null
 ,address1 varchar(50) not null
 ,city varchar(100) not null
 ,state varchar(2) not null
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


  
ALTER TABLE dbo.ChangeTrackingTest
enable change_tracking with (track_columns_updated=on);

GO

-- Initial insert.
insert into dbo.ChangeTrackingTest
(
  name 
 ,address1 
 ,city 
 ,state 
)
values
(
  'Kashyap'
 ,'9909 jj Jsds '
 ,'Konkan'
 ,'AP'
)
,(
  'Srini'
 ,'100 jj Jsds '
 ,'Konkan'
 ,'AP'
)
go
        
SELECT  
  CHANGE_TRACKING_CURRENT_VERSION()
 ,CHANGE_TRACKING_MIN_VALID_VERSION(object_id('dbo.ChangeTrackingTest','U'))
  
-- suppose i already have processed the change tracking extract upto the current version
-- which is 1 in my computer since i have just enabled the change tracking in my database
-- and the above is the first insert. 

-- The following command will not return anything since there are no changes so
-- far since version 1.

select * from changetable(changes dbo.ChangeTrackingTest,1) as a  

--Now i update Record 1 in change tracking context. The purpose of using context is that 
--my extract can eliminate the changes occurered in this context.


declare @context varbinary(10) = cast('test' as varbinary(10));

with change_tracking_context (@context)

update dbo.ChangeTrackingTest
set address1 =  '10000 In Context '
where ID = 1;

--Now there is one update since my last syncronization version which was 1
-- following query should return one record. and i am using the function to
--get the updated column in xml.

select 
* 
,dbo.ChangeColumns('dbo','ChangeTrackingTest',sys_change_columns) as updatedColumns
from changetable(changes dbo.ChangeTrackingTest,1) as a  

--There is another update in the same record, but some other column is
--is updated this time and there is no context used.

update dbo.ChangeTrackingTest
set city =  'Chennai'
where ID = 1;


-- following query should return one record. and i am using the function to
--get the updated column in xml. Notice that it now list two changed columns.
--If i put filter on the query to not include changes occurered in the context
--i expect it to not include address1 column.

select 
* 
,dbo.ChangeColumns('dbo','ChangeTrackingTest',sys_change_columns) as updatedColumns
from changetable(changes dbo.ChangeTrackingTest,1) as a  
where a.sys_change_context is null ;

/*
 Is there any way to exclude the column that is updated out side of the
 filter  context?
*/
 
 

---

DBA

Answer 3

Thanks for the code.

No, it does not work that way. First of all, you have:

select
* ,dbo.ChangeColumns('dbo','ChangeTrackingTest',sys_change_columns) as updatedColumns
from changetable(changes dbo.ChangeTrackingTest,1) as a
where a.sys_change_context is null ;

The WHERE clause cannot of course affect of what is placed in SYS_CHANGE_COLUMNS. All the WHERE clause is to filter out rows where SYS_CHANGE_CONTEXT.

As for what this WITH CHANGE_TRACKING_CONTEXT is good for, I am not really sure. CHANGETABLE(CHANGES) will return the most recently used context, there is no way to say "give me only the changes performed in this context".

I think the idea might be that the sync client retrieves changes from the table, and the updates some data back. Now, the sync client does not want to see its own next time it queries for changes, so it could use WITH CHANGE_TRACKING_CONTEXT, and then filter out rows from the change table which has its own context, as these rows has not been changed since last time.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Thanks Erland,

The SYS_CHANGE_COLUMNS is a computed column, it changes it contents based on the version that we put in with changetable function. that's why i was thinking that it should eliminate the column that doesn't fit in the filter. If i replace 1 with 2 in following query it will only return me the City column. 

I agree with your last comments, i am trying to implement the same but since i can not eliminate the column that has been updated in the context, it is very hard to implement conflict resolution, it is hard to know which columns got updated since last sync because the result also gives back the columns that updated by sync client. If somehow i know which columns have been touched since last synchronization in a record it would be easy to do conflict resolution. If sync client has touched column 2 and 3 and source has touched column 4 and 5. there is no  conflict.   

here is the situation that i am trying to solve.

table T1 is being change tracked in two databases D1 and D2 in two different servers. both feeds each other and use change tracking with context when update the target. 

Lets say D1 creates a record in T1. it does synchronization with D2 and now D2 has same record. When record gets created/updated in D2 using sync process , the change tracking context would be used so they don't get the chagnes they submitted to other database.

The inserts are strait forward. later, D1 updates column (2 and 3) in the same record and sync back with D2. it ask the changes from D2 and there are no changes so it  goes fine.

later D2 updates column 4 and D1 update column 2. now D1 tries to sync and it ask the changes since the last sync from D2, now it gets back the changes which says that column 2, 3 and 4 has been changed since last sync. It gets the column (2,3) which is part of the context. So, it thinks that the changes are conflicting when in reality D1 got its own change back and it thinks that they were made by D2.

---

DBA

Answer 5

It feels like context parameter should be a part of changetable function like version. 

---

DBA

Answer 6

It feels like context parameter should be a part of changetable function like version. 

Yes, the thought occurred to me too when I read through Books Online. The WITH CHANGE_TRACKING_CONTEXT things feels like some rump idea, which they never pursued to the end.

I'm afraid that I don't have a good solution for your update scenario.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Any progress?

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 8

We are having an onsite meeting with person from Microsoft to discuss ODS this month. I have asked her to discuss this forum with SQLCAT team and see if they can fix it or find some solutions. I will update the post once i have something new on the subject.

---

DBA