what happens to a change-data-capture instance when the underlying table is altered?

If I enable change-data-capture for a table, and then som开发者_高级运维ebody else adds a column to the table, will my capture be affected? Will I still get updates for the original columns? For the new column?

What if a column is deleted?

---

(This answer from my colleague Steve, but he doesn't have a SO account so I'll post it...)

If you ADD a column, the CDC does not change ... you continue to receive updates on the same column set as before.

If you DELETE a column, the CDC continues to capture changes, but the deleted column will always have the value NULL.

If you ALTER a column, the CDC continues to capture changes, but values in the altered column will have the new column type.

---

From MSDN's About Change Data Capture (SQL Server)

"Handling Changes to Source Tables To accommodate column changes in the source tables that are being tracked is a difficult issue for downstream consumers. Although enabling change data capture on a source table does not prevent such DDL changes from occurring, change data capture helps to mitigate the effect on consumers by allowing the delivered result sets that are returned through the API to remain unchanged even as the column structure of the underlying source table changes. This fixed column structure is also reflected in the underlying change table that the defined query functions access. To accommodate a fixed column structure change table, the capture process responsible for populating the change table will ignore any new columns that are not identified for capture when the source table was enabled for change data capture. If a tracked column is dropped, null values will be supplied for the column in the subsequent change entries. However, if an existing column undergoes a change in its data type, the change is propagated to the change table to ensure that the capture mechanism does not introduce data loss to tracked columns. The capture process also posts any detected changes to the column structure of tracked tables to the cdc.ddl_history table. Consumers wishing to be alerted of adjustments that might have to be made in downstream applications, use the stored procedure sys.sp_cdc_get_ddl_history. Typically, the current capture instance will continue to retain its shape when DDL changes are applied to its associated source table. However, it is possible to create a second capture instance for the table that reflects the new column structure. This allows the capture process to make changes to the same source table into two distinct change tables having two different column structures. Thus, while one change table can continue to feed current operational programs, the second one can drive a development environment that is trying to incorporate the new column data. Allowing the capture mechanism to populate both change tables in tandem means that a transition from one to the other can be accomplished without loss of change data. This can happen any time the two change data capture timelines overlap. When the transition is effected, the obsolete capture instance can be removed. Note Note The maximum number of capture instances that can be concurrently associated with a single source table is two."