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


















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