none
Adding default constraint not tracked by CDC

    Question

  • It appears CDC doesn't track data changes that results from adding a column with a default value. E.g. the below doesn't result in any rows in the capture instance.

    ALTER TABLE tableA ADD column1 INT default (0)

    I have a downstream system that must match the source database. So when a default value is added, the changed data isn't getting into CDC and the data downstream system is then different.

    What's the best way of dealing with default constraints in CDC?

    SQL Version: 

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64) 
    Feb  8 2013 10:37:00 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

    Ta.

    Jag


    • Moved by Tom Phillips Thursday, June 20, 2013 7:11 PM CDC question
    • Edited by jagt70 Thursday, June 20, 2013 9:00 PM
    Thursday, June 20, 2013 6:56 PM

All replies

  • Please post the results of SELECT @@VERSION.

    Thursday, June 20, 2013 7:13 PM
  • Added to original post:

    Microsoft SQL Server 2008 R2 (SP2) - 10.50.4276.0 (X64) 
    Feb  8 2013 10:37:00 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


    Thursday, June 20, 2013 9:01 PM
  • I am able to duplicate your issue in both SQL 2008 R2 and SQL 2012 11.0.3000. 

    I suspect it is a problem with how defaults are handled for non-null columns not being captured.

    I could not find a bug report on the issue.  I would suggest you log one at https://connect.microsoft.com/

    Thursday, June 20, 2013 9:34 PM