Skip to content

Insight and analysis of technology and business strategy

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 cdc.fn_cdc_get_all_changes_ function to query changes made to the table. cdc.fn_cdc_get_all_changes_ function returns all columns from the table, even when only one column was updated. It also returns __$update_mask column which is a bit mask that shows which columns were updated, but still - the whole row is returned. When calling this function with "all_update_old" option it returns two records for each update: one with "before update" values and one with "after update" values. The challenge was to produce a "log" table with columns like this:
commit_time column_name old_value new_value
UNPIVOT operator helps us to produce desired result. Here's an example query: [code lang="SQL" wraplines="false" highlight="9,17"] 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 [/code]

Top Categories

  • There are no suggestions because the search field is empty.

Tell us how we can help!

dba-cloud-services
Upcoming-Events-banner