Using UNPIVOT with CDC Functions to get Updated Columns List

Nov 29, 2010 / By Igor Raytsin

Tags: ,

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:

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

6 Responses to “Using UNPIVOT with CDC Functions to get Updated Columns List”

  • Gunarathinam says:

    Hi Igor Raytsin,

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

  • Febri says:

    Cool… thanks

  • sashank says:

    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.

    • Igor Raytsin says:

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

      Thank you,
      - Igor

      • sashank says:

        :)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.

        • sashank says:

          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….

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>