April 21, 2014 at 2:05 pm
Hi,
I am using SQL Server 2012 and to me a part of data captured by CDC is not making sense.
I have a table called 'Schema.Table1', and I enabled CDC on it by running 'sys.sp_cdc_enable_table'. I see that a table called 'cdc.Schema_Table1_CT' got created which now gets an entry when ever I Insert, Update or delete a record in the original table.
Till this point every thing works fine.
My original Table has a NOT NULL INT column called 'AuditTrackerUserID' with a default value of 1996. My application does not provides a value for this column, but because the column itself has a default value, records get inserted without error.
When I try to execute the following Query I see multiple records with __$operation of 3 and 1.
SELECT * from cdc.Schema_Table1_CT where AuditTrackerUserID IS NULL
My expectation is that I should not ever see any record returned by this query because AuditTrackerUserID is a not null column, but I do.
Can anyone please explain this behavior>
-Vivek.
April 21, 2014 at 4:15 pm
Well, __$operation = 1 is a deletion and __$operation = 3 is a value before an update. So I would image with a default value what you are seeing are two things:
- The rows with __$operation = 1 are recorded when those records are deleted from the source table
- The rows with __$operation = 3 are recorded when the row is initially inserted but before the default value gets applied
For update records you should see two entries for every update; one with values before the update and one with values after the update. Since you were only looking for NULL values for that column I think only the before value entries showed up, but if you did a more broad search I would think that you would see other entries with __$operation = 4 for the values after the update and the column having the default value.
About Change Data Capture (SQL Server)
Joie Andrew
"Since 1982"
April 21, 2014 at 9:18 pm
Thanks Joie for replying.
I am slightly confused with your explaination of __$Operation = 3.
The following article says:
1 = delete
2 = insert
3 = update (old values)Column data has row values before executing the update statement.
4 = update (new values)Column data has row values after executing the update statement.
http://technet.microsoft.com/en-us/library/bb500305.aspx
Based on the above explaination of various operation statuses and the fact that the column in the original table is not nullable.
It is suprising that
a)I will see a record getting deleted with AuditTrackerUserID = NULL when infact based on table definition a null in this column is not possible.
b)I don't see any records with __$Operation =2 and AuditTrackerUserID being NULL. therefore I am assuming that the entry for inserted records is fine i.e. the value in the concerned column is not null. In such a scenario where inserted records are correct then how come the value in this column before the update became NULL.
Again, I appreciate you trying to help me out here, but this behaviour is driving me crazy.
More over I see such records only on production. I can't reproduce this behaviour on either developement or QC environment.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy