Using UNPIVOT with CDC Functions to get Updated Columns List

Posted in: Technical Track

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_timecolumn_nameold_valuenew_value

UNPIVOT operator helps us to produce desired result.

Here’s an example query:

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

Interested in working with Igor? Schedule a tech call.

7 Comments. Leave new

Hi Igor Raytsin,

Super.. THis is what exactly i needed. Thanks a lot for sharing this.

Reply

Cool… thanks

Reply

Hello… Can you help me to make this query run.
I know this might be a generic solution but it will be great if you can point me to the changes I have to make to make this work for me.

Reply

Hello Sashank,
I’m not sure what query you are referring.
Could you please rephrase your question.

Thank you,
– Igor

Reply

:)Thanks for replying…I was talking about the query int he example.. It doesnt work for me. are there any changes i have to do before i can get this working.

Reply

i got it running now… but with one small issue… any help will be great…

I just get the column names in the fields old_value and new_value… was hoping to see the values in the column that changed.

example.. if col1 has changed i just see col1 in all column_nmae,old_value,new_value….

Reply

You saved my day buddy.. thank you very much… :)

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *