Do you know If is possible to track changes (CHANGE TRACKING or CDC) with 'Track columns updated' but only specific columns which we would like to track. The reason is that we have table with 300 columns and we would like to track only 5 of them.
1 Answer
Yes, CDC can track changes to only specified columns when you enable a table via the sys.sp_cdc_enable_table procedure as per the captured_column_list parameter:
Identifies the source table columns that are to be included in the change table. captured_column_list is nvarchar(max) and can be NULL. If NULL, all columns are included in the change table.
For more information on enabling CDC and specifying only a subset of columns to track you can read Change Data Capture for auditing SQL Server which also mentions:
After enabling CDC at the database level, we can enable it to track and audit the DML changes on the database tables, by a member of db_owner fixed database role, using the sys.sp_cdc_enable_table system stored procedure, with the ability to enable it to track changes on list of columns specified by the @captured_column_list parameter...
