CDC at column level in sql server uing T-SQL

CDC is one of the best feature to track about insert, update and delete records with values. This feature  is most helpful in DW incremental load. Same feature is also created using Audit tables and firing triggers but this feature is more faster to implement.

To demonstrate on cdc how it works in sql database we will first create sample table A

Create table A(
    AId int NOT NULL,
    Aname nvarchar(255) NOT NULL,
    Adds int NOT NULL,
    City nvarchar(255)
)

We require primary key constraint on first column Aid.

Alter table A
    Add Constraint PK_A
    Primary Key Clustered (
        Aid ASC )

If you Primary Key in table it must be included in column level CDC.

Inserting records in table

Insert into A
    values (100,'L1',2,'P'),
            (101,'L2',20,'F'),
            (102,'L3',12,'P')

Now for using CDC we first need to enable database

EXEC sys.sp_cdc_enable_db

Above statement will enable you current database with CDC

select name, is_cdc_enabled from sys.databases

Now we will enable CDC only for 2 columns Aid and AName in above table A

exec sys.sp_cdc_enable_table
    @source_schema = 'dbo',
    @source_name = 'A' ,
    @role_name = 'CDCRole',
    @captured_column_list= '[AId],[AName]'

When you enable CDC you can see CDCRole enble in you server.

select name, type, type_desc, is_tracked_by_cdc from sys.tables

Above sql statement will show you your table is enabled or not in your database?

insert into dbo.A values (3,'L7',22,'P')
insert into dbo.A values (4,'L8',32,'F')

Lets insert few records

declare @Start_lsn binary(10), @end_lsn binary(10)
select @start_lsn = sys.fn_cdc_get_min_lsn('dbo_A')
select @end_lsn = sys.fn_cdc_get_max_lsn()
select * from cdc.fn_cdc_get_net_changes_dbo_A(@start_lsn, @end_lsn, 'all');
select * from cdc.fn_cdc_get_all_changes_dbo_A(@start_lsn, @end_lsn, 'all');

Above statement will show you all records that are inserted, deleted or updated after enabling CDC in sql server.

You can disable you table A using below query

exec sys.sp_cdc_disable_table
    @source_schema = 'dbo',
    @source_name = 'A' ,
    @capture_instance = 'dbo_A'

And can disable database using SQL

EXEC sys.sp_cdc_disable_db