none
Change Data Capture: saving space within CT tables

    Question

  • When I enable data capture on a table a new table gets created to keep the changes in (cdc.schema_TableName_CT). That's a lot of duplicate data. Is it possible to somehow reduce the number of columns in the _CT table to the absolute minimum (unique key)? Having this unique row identifier you can select the rows in your original table during your ETL process and use the data in there to update/delete/insert in your DWH.

    At this moment, if you remove some columns from tracking, no changes are being captured. That sounds obvious of course.
    I just want to save disk space for large fast changing tables.

    Wednesday, June 09, 2010 2:23 PM

All replies

  • you could reduce the number of columns that need to be tracked for changes, for example, there is no point capturing changes in last modified timestamp columns that have defaults like getdate()

    You could also consider reducing the retention period of the cleanup job.

    Thursday, February 23, 2012 11:02 AM
  • If the unique key is all you require you could use change tracking instead of CDC.

    Thursday, March 08, 2012 8:36 PM