CODEWITHSUNDEEP
sql-serverchange-data-capture
ajamrozek
ajamrozek

Reputation: 190

CDC tracking changes made to a column that wasn't changed

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.

Upvotes: 0

Views: 2344

Answers (1)

ajamrozek
ajamrozek

Reputation: 190

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.

Upvotes: 2

Related Questions