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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply