Just thought of posting this in case someone else is looking at a way to decrypt the __$update_mask.

If you don’t know what an __$update_mask is then you should go through any one of the following articles first:

Change Data Capture in SQL Server 2008

Introduction to Change Data Capture (CDC) in SQL Server 2008

Using Change Data Capture (CDC) in SQL Server 2008

I came across various articles that explained what an __$update_mask is but none of those articles explained how to convert the hexadecimal value into the bit mask value in a query to determine which column has changed. So here it goes:

The two functions sys.fn_cdc_get_column_ordinal and sys.fn_cdc_is_bit_set have all the magic inside them that tell you whether or not a column has been updated. I will try to explain this with an example:

Scenario:

We have a table called Subscriptions with two columns Email and Status tracked by CDC. We want to determine whether or not Status was changed to ‘Cancelled’ from ‘Active’ in a given date range.

Solution:

DECLARE @StartDate datetime = '2012-01-18';
DECLARE @EndDate datetime = '2012-01-19';
DECLARE @begin_lsn BINARY(10), @end_lsn BINARY(10);

-- Set the LSN values
SELECT @begin_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than', @StartDate);
SELECT @end_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @EndDate);

-- Get all updated rows where Status was updated
SELECT [RegistrationCode], [Email], [Status]
FROM cdc.fn_cdc_get_all_changes_dbo_Subscription(@begin_lsn,@end_lsn,'all')
WHERE __$operation = 4
AND sys.fn_cdc_is_bit_set (

	sys.fn_cdc_get_column_ordinal ('dbo_Subscription','Status'),__$update_mask

		) = 1

Explanation:

The function sys.fn_cdc_get_column_ordinal returns column ordinal i.e. position of the column within the table (3 in our example for [Status]). The sys.fn_cdc_is_bit_set then checks the bit mask in each changed row for that colum ordinal. If the bit mask is 1 that means the value of that column has changed in the row.

Read more on the MSDN about the CDC functions available:

Change Data Capture Functions (Transact-SQL)