cdc.<capture_instance>_CT (Transact-SQL)
Is the change table created when change data capture is enabled on a source table. The table returns one row for each insert and delete operation performed against the source table, and two rows for each update operation performed against the source table. When the name of the change table is not specified at the time the source table is enabled, the name is derived. The format of the name is cdc.capture_instance_CT where capture_instance is the schema name of the source table and the source table name in the format schema_table. For example, if the table Person.Address in the AdventureWorks sample database is enabled for change data capture, the derived change table name would be cdc.Person_Address_CT.
We recommend that you do not query the system tables directly. Instead, execute the cdc.fn_cdc_get_all_changes_<capture_instance> and cdc.fn_cdc_get_net_changes_<capture_instance> functions.
Column name |
Data type |
Description |
|---|---|---|
__$start_lsn |
binary(10) |
Log sequence number (LSN) associated with the commit transaction for the change. All changes committed in the same transaction share the same commit LSN. For example, if a delete operation on the source table removes two rows, the change table will contain two rows, each with the same __$start_lsn value. |
__$end_lsn |
binary(10) |
Identified for informational purposes only. Not supported. Future compatibility is not guaranteed. In SQL Server 2008, this column is always NULL. |
__$seqval |
binary(10) |
Sequence value used to order the row changes within a transaction. |
__$operation |
int |
Identifies the data manipulation language (DML) operation associated with the change. Can be one of the following: 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. |
__$update_mask |
varbinary(128) |
A bit mask based upon the column ordinals of the change table identifying those columns that changed. |
<captured source table columns> |
varies |
The remaining columns in the change table are the columns from the source table that were identified as captured columns when the capture instance was created. If no columns were specified in the captured column list, all columns in the source table are included in this table. |
Remarks
Captured Column Data Types
Captured columns included in this table have the same data type and value as their corresponding source columns with the following exceptions:
Timestamp columns are defined as binary(8).
Identity columns are defined as either int or bigint.
However, the values in these columns are the same as the source column values.
In this release, data types introduced in SQL Server 2008 are not supported.
Large Object Data Types
For the LOB data types varchar(max), nvarchar(max), varbinary(max), image, text, ntext, and xml, the old value will only appear in the update old row if the column actually changed during update. For other data types, the column value will always appear in both update rows.
By default, the maximum size that can be added to a captured column in a single INSERT, UPDATE, WRITETEXT, or UPDATETEXT statement is 65,536 bytes or 64 KB. To increase this size to support larger LOB data, use the max text repl size option to specify a larger maximum size. For more information, see How to: Configure the max text repl size Option (SQL Server Management Studio).
Data Definition Language Modifications
DDL modifications to the source table, such as adding or dropping columns, are recorded in the cdc.ddl_history table. These changes are not applied to the change table. That is, the definition of the change table remains constant. When inserting rows into the change table, the capture process ignores those columns that do not appear in the captured column list associated with the source table. If a column appears in the captured column list that is no longer in the source table, the column is assigned a null value.
Changing the data type of a column in the source table is also recorded in the cdc.ddl_history table. However, this change does alter the definition of the change table. The data type of the captured column in the change table is modified when the capture process encounters the log record for the DDL change made to the source table.
If you need to modify the data type of a captured column in the source table in a way that decreases the size of the data type, use the following procedure to ensure that the equivalent column in the change table can be successfully modified.
In the source table, update the values in the column to be modified to fit into the planned data type size. For example, if you change the data type from int to smallint, update the values to a size that fits in the smallint range, -32,768 to 32,767.
In the change table, perform the same update operation to the equivalent column.
Alter the source table by specifying the new data type. The data type change is propagated successfully to the change table.
Data Manipulation Language Modifications
When insert, update, and delete operations are performed on a change data capture enabled source table, a record of those DML operations appears in the database transaction log. The change data capture capture process retrieves information about those changes from the transaction log, and adds either one or two rows to the change table to record the change. Entries are added to the change table in the same order that they were committed to the source table, although the commit of change table entries must typically be performed on a group of changes instead of for a single entry.
Within the change table entry, the __$start_lsn column is used to record the commit LSN that is associated with the change to the source table, and the __$seqval column is used to order the change within its transaction. Together, these metadata columns can be used to make sure that the commit order of the source changes is preserved. Because the capture process obtains its change information from the transaction log, it is important to note that change table entries do not appear synchronously with their corresponding source table changes. Instead, corresponding changes appear asynchronously, after the capture process has processed the relevant change entries from the transaction log.
For insert and delete operations, all the bits in the update mask are set. For update operations, the update mask in both the update old and update new rows will be modified to reflect the columns that changed during update.