0

I have a table with CDC enabled that's throwing the following weird behavior. In an update where one of three nullable columns already has a value [8,23|NULL|NULL] and I update only the other two columns [AlexJ, 1], CDC tracks a change against all three columns. 

2018-06-22 13:55:37.763 NULL    NULL    NULL 
2018-06-22 13:55:37.763 8,23    AlexJ   1

I use a templated query to get these data from the cdc.dbo_Tablename_CT table.

...
SELECT sys.fn_cdc_map_lsn_to_time([__$start_lsn]) AS 'ModifiedDate', 
    [Tags],[ModifiedBy], [IsInactive]
FROM cdc.fn_cdc_get_all_changes_dbo_Tablename
    (@from_lsn, @to_lsn, N'all update old')
WHERE Id = @Id
...

How do I get around this? It's most annoying and may direct me away from using CDC, not that deploying and maintaining a CDC'ed table is a walk in the park in the best of times.

ajamrozek
  • 160
  • 10
  • Are you sure it isn't being updated? Can you post your update statement? It doesn't track actual data changes, it tracks update statements. So updating it to the same value is the same as changing the value. – Sean Lange Jun 22 '18 at 20:26
  • Positive. I first noticed it using EF core Linq updates, then used the "Edit 200 Rows" data editor in SSMS, then wrote a raw update statement that only included the two columns. Same behavior all ways. – ajamrozek Jun 22 '18 at 20:32
  • Well both EF and Edit top 200 will update every column. – Sean Lange Jun 22 '18 at 20:49
  • So then why don't other columns show up in the CT table changes? Because they're not nullable? Even though EF "updates" every column, if the value is identical is it really CDC'ed? – ajamrozek Jun 22 '18 at 20:52

1 Answers1

2

https://learn.microsoft.com/en-us/sql/relational-databases/system-tables/cdc-capture-instance-ct-transact-sql?view=sql-server-2017

It’s always going to put null for varchar(max) … the tags column.

Large Object Data Types Columns of data type image, text, and ntext are always assigned a NULL value when _$operation = 1 or _$operation = 3. Columns of data type varbinary(max), varchar(max), or nvarchar(max) are assigned a NULL value when __$operation = 3 unless the column changed during the update. When __$operation = 1, these columns are assigned their value at the time of the delete. Computed columns that are included in a capture instance always have a value of NULL.

ajamrozek
  • 160
  • 10