The ability to detect the changed data in source systems and capture these changes is called Change Data Capture (CDC).
CDC is one of the new data tracking and capturing features of SQL Server 2008. It only tracks changes in user-created tables. Because captured data is then stored in relational tables, it can be easily accessed and retrieved subsequently, using regular T-SQL.
When we apply Change Data Capture features on a database table, a mirror of the tracked table is created with the same column structure of the original table, but with additional columns that include the metadata used to summarize the nature of the change in the database table row.
If we have n columns in our table then mirror of the tracked table will have n columns including the metadata columns like operation.
–Enabling the CDC for Database
–Enabling the CDC for table
@source_schema = N’yourschema’,
@source_name = N’yourtable’,
@role_name = NULL
Let us create one table (test) with 2 columns (id, name) and enable CDC feature. Then the tracking table structure looks like follows.
Step 1. Inserting the values into test table
Step 2. Updating the name from “abc” to “xyz”
Step 3. Delete the record
Step 4. Inserting new record with id as 2 and name as “def”
In the above table we can able to see “_$operation” column having values 1,2,3,4. Their denomination as follows.
Delete Statement = 1, Insert Statement = 2, Value before Update Statement = 3, Value after Update Statement = 4.
Like the above cases, we can do tracking for different tables whose need tracking.
In addition to the above table, there are other 5 tables to store ddl history, indexes for change tables, etc., will come when the CDC feature enabled.
Please feel free to add your comments/suggestions to share the knowledge.