Using UNPIVOT with CDC Functions to get Updated Columns.List

Microsoft introduced Change Data Capture (CDC) technology in SQL Server 2008. This technology captures DML (insert/update/delete) changes to a table. After CDC is enabled for a database and a given table, one can use the cdc.fn_cdc_get_all_changes_ function to query changes made to the table.

Introduction to SQL Server Change Data Capture (CDC)

Capturing DML Changes

The cdc.fn_cdc_get_all_changes_ function returns all columns from the table, even when only one column was updated. It also returns the __$update_mask column, which is a bit mask that shows which columns were updated, but the whole row is still returned.

Understanding the Row-Based Output

When calling this function with the "all_update_old" option, it returns two records for each update: one with "before update" values and one with "after update" values. This provides a comprehensive view of the data state at the time of the transaction.

The Challenge of Granular Auditing

Limitations of Standard CDC Functions

While the standard output is useful, the challenge was to produce a specific "log" table structure with the following columns:

• commit_time
• column_name
• old_value
• new_value

Solution: Transforming Data with UNPIVOT

Leveraging the UNPIVOT Operator

The UNPIVOT operator helps us transform the wide row-based data into the desired normalized result. By isolating the changed columns using the update mask and then unpivoting the values, we can create a clear audit trail of specific column-level changes.

Implementation Query Example

Here is an example query that combines the CDC function with UNPIVOT to generate a detailed change log:

SELECT      sys.fn_cdc_map_lsn_to_time(up_b.__$start_lsn) as commit_time,      up_b.column_name,      up_b.old_value,      up_a.new_value  FROM (      SELECT __$start_lsn, column_name, old_value      FROM (         SELECT              __$start_lsn,              CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1)                   THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1],              CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1)                   THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2],              ....          FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all update old')          WHERE __$operation = 3     ) as t1      UNPIVOT (old_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp ) as up_b -- before update  INNER JOIN (     SELECT __$start_lsn, column_name, new_value      FROM (         SELECT              __$start_lsn,              CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_1'),__$update_mask) = 1)                   THEN CAST([CDC_column_1] as sql_variant) ELSE NULL END AS [CDC_column_1],              CASE WHEN (sys.fn_cdc_is_bit_set (sys.fn_cdc_get_column_ordinal ('dbo_','CDC_column_2'),__$update_mask) = 1)                   THEN CAST ([CDC_column_2] as sql_variant) ELSE NULL END AS [CDC_column_2],              ....          FROM cdc.fn_cdc_get_all_changes_dbo_(@from_lsn, @to_lsn, N'all') -- 'all update old' is not necessary here          WHERE __$operation = 4     ) as t2      UNPIVOT (new_value FOR column_name IN ([CDC_column_1], [CDC_column_2], ...) ) as unp  ) as up_a -- after update  ON up_b.__$start_lsn = up_a.__$start_lsn  AND up_b.column_name = up_a.column_name

SQL Server Database Consulting Services

Ready to future-proof your SQL Server investment?