Search

Incorrect documentation regarding CDC and text/ntext/image by sab39

Closed
as Fixed Help for as Fixed

1
0
Sign in
to vote
Type: Bug
ID: 774272
Opened: 12/13/2012 11:05:14 AM
Access Restriction: Public
0
Workaround(s)
0
User(s) can reproduce this bug
There is incorrect documentation on this page: http://msdn.microsoft.com/en-us/library/bb500305.aspx.

That page states the following: "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."

However, this is incorrect. The correct information is given in a different page, http://msdn.microsoft.com/en-us/library/bb510627.aspx, which correctly states: "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."

So, the original documentation is right about the var*(max) types, but wrongly states that the values are preserved for image, text and ntext when the values are changed, when in fact those are always assigned NULL. And I wouldn't want to even speculate, given this contradictory documentation, on what the actual behavior is of xml-typed columns.

See also this question http://social.msdn.microsoft.com/Forums/en-SG/sqldatabaseengine/thread/0b6e9e25-24d7-48d0-b1e8-a9fd905082d3?prof=required which points out the correct docs (I commented about the incorrect ones there too).
Details (expand)

Product Language

English

Version

SQL Server 2012 - Standard Edition

Category

Documentation

Operating System

Not Applicable

Operating System Language

US English

Steps to Reproduce

Create a table with an ntext column. Insert some data with a non-null value for the ntext column. Enable CDC on that table. Update the value of the ntext column. Look at the contents of the cdc.<table_name>_CT table.

Actual Results

The "update before" row, with __$operation = 3, has null in the "before" value for the ntext column, where http://msdn.microsoft.com/en-us/library/bb500305.aspx says it should have the value that was present before the update, since the column had changed.

Expected Results

The documentation should reflect the actual behavior. The documentation at http://msdn.microsoft.com/en-us/library/bb510627.aspx seems to be correct, so I suggest using that language.

(The documentation seems to be equivalently incorrect for all versions of SQL server - the SQL 2012, SQL 2008 and SQL 2008 R2 versions of the page all have the same incorrect information)

Platform

 

Virtualization

 
File Attachments
0 attachments
Sign in to post a comment.
Posted by Microsoft on 1/8/2013 at 1:16 PM
Made the change you suggested. This will appear in a few weeks for SQL Server 2012 Books Online. Probably in a couple of months for SQL Server 2008 R2. Thank you.
Posted by Microsoft on 12/26/2012 at 8:32 AM
Thank you for submitting this. I'll look into it.
Sign in to post a workaround.