Skip to main content

Create Change Data Capture ( CDC ) in SQL Server

Hi,
   Change Data Capture records insert, update, and delete activity that is applied to a SQL Server Databases and Tables. Change data capture must be explicitly enabled for the database manually, by default it is disabled.  After creating the CDC for table, Change Table (_CT) is automatically created under System table section as _CT appending to the original table name. This table contains all the modified, pre modified, newly inserted, deleted records with their details. This works from SQL Server2008 and works with Admin and should have SQL Server Agent.

Enable CDC at DB Level
Enable CDC at Table Level
USE DataBaseName
GO
declare @rc int
exec @rc = sys.sp_cdc_enable_db
select @rc

USE DataBaseName
GO
EXEC sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'TableName' ,
    @role_name = NULL
GO


After enableing CDC you can find the additional table as dbo.TableName_CT . you can find the table in Tables -> System tables in your database.

Lets See what the newly created table has.
  • __$start_lsn
  • __$end_lsn
  • __$seqval
  • __$operation
  • __$update_mask
The column  _$operation contains value which corresponds to DML Operations. Following is quick list of value and its corresponding meaning.
  • Delete Statement = 1
  • Insert Statement = 2
  • Value before Update Statement = 3
  • Value after Update Statement = 4 
The column _$update_mask shows, via a bitmap,   which columns were updated in the DML operation that was specified by _$operation.  If this was  a DELETE or INSERT operation,   all columns are updated and so the mask contains value which has all 1’s in it. This mask is contains value which is formed with Bit values
Now you can make changes in your original table by Inserting, updating and deleting and find the changes in backup table  as cdc.dbo_tableName_CT

View all the modified records by :   select * from cdc.dbo_YourTableName_CT



You can notice the system generated colums in the retived data.
_$update_mask shows via a bitmap, which columns are updated in DML operation.

In _$operation column 2 Shows for Insertion, 3 Shows data before updation of record, 4 Shows data after updation of record.


Comments

  1. Thanks for sharing an informative blog keep rocking bring more details.I like the helpful info you provide in your articles. I’ll bookmark your weblog and check again here regularly. I am quite sure I will learn much new stuff right here! Good luck for the next!
    web designing classes in chennai | web designing training institute in chennai
    web designing and development course in chennai | web designing courses in Chennai
    best institute for web designing in chennai | web designing course with placement in chennai
    Web Designing Class
    web designing course
    best institute for web designing

    ReplyDelete
  2. Top Courses to learn
    Excellent blog with lots of information. I have to thank for this. Do share more.

    ReplyDelete
  3. Top Technologies to learn

    Excellent blog with lots of information. I have to thank for this. Do share more.

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Great!it is really nice blog information.after a long time i have grow through such kind of ideas.
    thanks for share your thoughts with us. need some more info
    Ai & Artificial Intelligence Course in Chennai
    PHP Training in Chennai
    Ethical Hacking Course in Chennai Blue Prism Training in Chennai
    UiPath Training in Chennai

    ReplyDelete

Post a Comment