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

In this article, we will go bit deep and understand few more features and commands of CDC (Change Data Capture).

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:

disable CDC

CDC can be disabled for entire database using the following:

CDC

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:

separate filegroup

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:

run query

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.

specified LSN

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:

query

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

Employees 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: