My article written on a subject of Introduction to Change Data Capture (CDC) in SQL Server 2008 is quite a popular and I have received many requests for uploading the script associated with this subject.
Change Data Capture records INSERTs, UPDATEs, and DELETEs applied to SQL Server tables, and makes a record available of what changed, where, and when, in simple relational ‘change tables’ rather than in an esoteric chopped salad of XML. These change tables contain columns that reflect the column structure of the source table you have chosen to track, along with the metadata needed to understand the changes that have been made. Pinal Dave explains all, with plenty of examples in a simple introduction.
SQL Server 2005 introduced the new features of ‘after update’, ‘after insert’ and ‘after delete’ triggers that almost solved the problem of tracking changes in data.  A better solution was introduced in SQL Server 2008 and is called Change Data Capture (CDC). CDC has allowed SQL Server developers to deliver SQL Server data archiving and capturing without any additional programming.
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.
Download the Script
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Fundoo and must read Article..
Thanks for Sharing this script,
This is very useful feature.
Thank you very much. It is very useful. I need it.
This was a great article… I am looking for any inputs on what CDC does the performance. Also, how do we go about cleaning up the Sys tables – do we need to use the same archive/purge strategy like the live tables?
We found that CDC did have a performance hit during large insert or updates but this was an acceptable performance loss for what was being tracked.
Pinal I can’t get it
actually i can’t download any of ur downloads… :(
plz help me
Hi Pinal,
I want to Capture who altered data in the CDC table.
Im able to get windows AD level users, but i cant get it from database table level users
Hi Pinal,
I want to Capture users in CDC table,
I have users table with 5 users and application using 1 ID to connect to the database.
How can i add a column to capture users logins.
I have used CDC but found that in 2008R2 it was still not prime time ready. I haven’t had a chance to review the 2012 version.
Where is the script link?
Please check again, I have fixed the link!