SQL Server 2008 Change Data Capture stores NULL for ntext fields on update

Question

I couldn't find anything related to this so far. I am implementing the Change Data Capture features on a SQL 2008 R2 server, and I've noticed some odd behavior for fields with a ntext datatype. I've enabled CDC on a table, and I want to track the values of the columns on an update, so the SQL Server generated cdc table writes out two records. One for the data before the change, and one after. The record with the _$operation type of 3 (i.e. before the change) shows any columns that are of type ntext to have NULL values, rather than the actual values that the column held. Columns of other datatypes show as expected. Maybe this is the expected behavior because of the datatype, but I couldn't find any documentation that leads me to believe that should be the case. Does this seem correct to anyone else? Thanks.

Answer 1

http://msdn.microsoft.com/en-us/library/bb510627.aspx

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.

---

Chuck

Answer 2

Thank you. I wonder what the technical limitation is that causes that to be by design?

Answer 3

http://msdn.microsoft.com/en-us/library/bb500305.aspx has incorrect information, then - it states that "For the LOB data types varchar(max), nvarchar(max), varbinary(max), image, text, ntext, and xml, the old value will only appear in the update old row if the column actually changed during update. For other data types, the column value will always appear in both update rows."

Apparently it's correct about varbinary(max), varchar(max) and nvarchar(max) but incorrect about image, text and ntext, which actually (as you and the questioner point out) have null in the update-old row and in delete rows regardless.

(And, given these pieces of contradictory documentation, I wouldn't even want to speculate about what the actual behavior of xml columns is! Fortunately it doesn't matter for my use case)

Is there a process for getting incorrect documentation updated?

Answer 4

Just answering your last point - I think for SQL Server documentation problems it's best to post in the SQL Documentation forum here on MSDN. This forum is frequently visited by people responsible for documentation, so you can get the best support and fixes there.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog