SQL SERVER – Download Script of Change Data Capture (CDC)

SQL
11 Comments

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.

SQL SERVER - Download Script of Change Data Capture (CDC) 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.

Download the Script

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQLAuthority News – Baby SQLAuthority is here!
Next Post
SQL SERVER – What is Data Mining – A Simple Introductory Note

Related Posts

11 Comments. Leave new

  • Fundoo and must read Article..

    Reply
  • Thanks for Sharing this script,
    This is very useful feature.

    Reply
  • Thank you very much. It is very useful. I need it.

    Reply
  • Srinivasan Prasanna
    August 2, 2010 10:28 pm

    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?

    Reply
    • 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.

      Reply
  • Pinal I can’t get it
    actually i can’t download any of ur downloads… :(
    plz help me

    Reply
  • 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

    Reply
  • 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.

    Reply
  • 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.

    Reply
  • Where is the script link?

    Reply

Leave a Reply

Menu