SQL Server 2008-Change Data Capture

One of the neat features available in SQL Server 2008 is Change Data Capture (CDC), there is also an other feature called Change Data Tracking. The Change data capture feature provides historical information for a user table by capturing data changes that were made. This feature allows the developer to indicate which columns in a particular table needs to be captured. This feature allows to build in audit capabilites which could be used for compliance purposes. In order to use CDC, one has to enable the database to perform Change Data Capture: This can be done by:
Use DatabaseName
GO
EXECUTE sys.sp_cdc_enable_db;
GO
Once this is done, One can selectively enable Change Data Capture on tables that need to be audited, this can be done by:


EXEC sys.sp_cdc_enable_table
@source_schema = N'Test'
,@source_name = N'Test_Table'
,@role_name = N'dbo'
,@capture_instance = N'tbl_audit_Test_Table'
,@captured_column_list = N'Col1Primarykey,Col2,Col3,Col4
,@filegroup_name = N'PRIMARY';
GO

One of the requirement here is that there needs to be a Primary key defined on the Source Table. Here the Test_table has Col1 has the Primary key.
This would enable CDC on the table called Test_table, the audit data would be captured in the database object called tbl_audit_test_table_CT(_CT is added to the name given in the capture instance by the system) , in order to query the data in this, the Select statement would be

SELECT Col1,Col2,Col3,Col4 FROM cdc.tbl_test_table_CT.

There are supporting functions which can be used to access data from the captured instance. MSDN and Books line have lot of information regarding these.

In order to Disable CDC on a table run this command:

EXECUTE sys.sp_cdc_disable_table
@source_schema =N'Test',
@source_name = N'Test_Table',
@capture_instance = N'tbl_audit_Test_Table';

To disable CDC on the Database, run the following command:
USE DatabaseName
GO
EXECUTE sys.sp_cdc_disable_db;
GO