Introduction To CDC (Change Data Capture) Of SQL Server - Part Two

In the previous article on Change Data Capture feature Of SQL Server, we had a look into CDC, enabled tracking for a table and checked its change history. Here, we will go bit deep and understand few more features and commands of it.

We can disable CDC on a table using the following command:



CDC can be disabled for entire database using the following:



As per CDC design, we can’t store audit tables in different database. But, it’s recommended to store those in a separate filegroup for better performance using @filegroup_name option:



By default, CDC tracks all columns of a table on which it is enabled. If we want to audit few specific columns than we can use option @captured_column_list with value as list of column names separated by comma and it should include primary key as shown below:



Since it is not recommended to query audit tables directly, we can use built-in SPs and functions provided by CDC for getting and analyzing audit data. Let’s understand few of them using LSN [Log sequence number used in SQL Server transaction log for auditing changes], which are used quite often:

1. sys.fn_cdc_get_min_lsn: Returns least LSN of specific capture instance
2. sys.fn_cdc_get_max_lsn: Returns highest LSN of all capture instances
3. sys. fn_cdc_map_lsn_to_time: Returns timestamp of specific LSN
4. fn_cdc_get_all_changes_<instance name>: Returns all changes made to that instance between specified LSN or time.

We have a feature known as net changes, on enabling it on table [which should have primary or unique key] will return single row that reflect final content after multiple changes done within a LSN range. This can be enabled by below query with option supports_net_changes as 1:



Let’s understand how to work doing few updates to employee table:



We don’t have change details of Ravi’s record [it is deleted] in output of fn_cdc_get_net_changes_employee_instance, because of enabling net changes.

I am ending the things here. In next article, we will drill down more on CDC. I hope this article will be helpful for all.

Read more articles on SQL Server:

• Improving SQL Performance Using Covering Indexes
  
• Get SQL Server Table Records in XML