In my earlier article, I have explained how to enable Change Data Capture (CDC) features for any database and its tables in few easy steps. It seems to me that it is very simple and straight forward activity. Just few lines of codes can give you a kick start. The next step is to avail the benefits of CDC, when you perform a series of DML (Data Manipulation Language) statements and CDC records, it automatically?? without a single line of code.
Let me execute INSERT / UPDATE / DELETE statements against the particular tables, which we have enabled for CDC in the earlier article to demonstrate how CDC works.
Please NOTE that before proceeding with CDC you MUST make sure that your SQL SERVER AGENT IS UP AND RUNNING.
- Impact of INSERT STATEMENT in CDC:
Let me pass an INSERT statement and observe how CDC records its changes in the audit tables as shown below.
--This script is compatible with SQL Server 2008 and above.
USE [AdventureWorks2012]
GO
--Insert a record in [HumanResources].[Department]
INSERT INTO [HumanResources].[Department]
([Name]
,[GroupName]
,[ModifiedDate])
VALUES
('IT'
,'R & D'
,GETDATE())
GO
Once you insert a new record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.
--This script is compatible with SQL Server 2008 and above. USE AdventureWorks2012 GO SELECT * FROM HumanResources.Department GO SELECT * FROM cdc.HumanResources_Department_CT GO --OUTPUT
As you can observe from the above result set, it has one new record in [HumanResources].[Department] as usual. In addition, one record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 2 and it means that it has been inserted by INSERT statement.
- Impact of UPDATE STATEMENT in CDC:
Let me pass an UPDATE statement and observe how CDC records its changes in the audit tables as shown below.
--This script is compatible with SQL Server 2008 and above. USE [AdventureWorks2012] GO --Update a record in [HumanResources].[Department] USE [AdventureWorks2012] GO UPDATE [HumanResources].[Department] SET [Name] = 'IT Support' WHERE [DepartmentID]=17 GO
Once you UPDATE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.
--This script is compatible with SQL Server 2008 and above. USE AdventureWorks2012 GO SELECT * FROM HumanResources.Department GO SELECT * FROM cdc.HumanResources_Department_CT GO --OUTPUT
As you can observe from the above result set, it has one updated record in [HumanResources].[Department] as usual. In addition, two records have been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 3 & 4 and it means that it has been inserted by UPDATE statement. But what do you mean by 3 & 4. In fact 3 means the data before the update statement and 4 means the data after the update statement.
- Impact of DELETE STATEMENT in CDC:
Let me pass a DELETE statement and observe how CDC records its changes in the audit tables as shown below.
--This script is compatible with SQL Server 2008 and above. USE [AdventureWorks2012] GO --Update a record in [HumanResources].[Department] USE [AdventureWorks2012] GO DELETE from [HumanResources].[Department] WHERE [DepartmentID]=17 GO
Once you DELETE a record in the table ([HumanResources].[Department]), lets review the table ([HumanResources].[Department]) and its relevant audit table (cdc.HumanResources_Department_CT) respectively.
--This script is compatible with SQL Server 2008 and above. USE AdventureWorks2012 GO SELECT * FROM HumanResources.Department GO SELECT * FROM cdc.HumanResources_Department_CT GO --OUTPUT
As you can observe from the above result set, it has DELETED one record in [HumanResources].[Department] as usual. In addition, ONE record has been inserted in the audit table (cdc.HumanResources_Department_CT) as well with some additional fields and ONE of the most important additional fields is _$operation that will explain you which operation inserted this record in the audit table. In the above result set say, _$operation has a value of 1 and it means that it has been inserted by DELETE statement.
In my upcoming article, I will write how to DISABLE the CDC feature.
An update of my blog is available at my twitter or you can like my Facebook page or subscribe via email by mentioning your email address in the ‘follow blog’ section.
SQL Server Portal 