Articles → SQL SERVER → CDC In SQL Server

CDC In SQL Server






What Is CDC?





Software Requirement





Table




set ansi_nulls on 
go 
set quoted_identifier on 
go 
set ansi_padding on 
go 
create table [dbo].[testtable](
    [id] [int] identity(1, 1) not null, 
    [name] [varchar](50) null, 
    [address] [varchar](50) null, 
    constraint [pk_testtable] primary key clustered ([id] asc) with (
      pad_index = off, statistics_norecompute = off, 
      ignore_dup_key = off, allow_row_locks = on, 
      allow_page_locks = on
    ) on [primary]
  ) on [primary] 
go 
set ansi_padding off 
go





Enabling CDC




exec sys.sp_cdc_enable_db  
GO






Picture showing CDC schema created after enabling CDC
Click to Enlarge



EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'TestTable',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO




Picture showing the tables created after CDC is enabled for TestTable
Click to Enlarge



Picture showing the dbo_TestTable_CT table schema
Click to Enlarge



  1. Meta columns – Columns from _@start_lsn to _$update_mask (except _$operation) used for CDC internally
  2. _$operation column - Contains following values: -
    1. 2 for insert
    2. 3 for before update
    3. 4 for after update
    4. 1 for delete
  3. "TestTable" table columns – all columns of "TestTable" are added in this table

Scripts




-- Insert Query
Insert into dbo.TestTable Values('Karan', 'India')

-- Update Query
Update dbo.TestTable 
Set name = 'Sumit' where address = 'India'

-- Delete Query
delete from dbo.TestTable where address = 'India'



Output




Picture showing the table data in CDC tables once CRUD operation is performed on TestTable
Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Saturday, May 27, 2017

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250