sys-fn-cdc-is-bit-set-transact-sql.md

title	sys.fn_cdc_is_bit_set (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/14/2017
ms.prod	sql-non-specified
ms.reviewer
ms.suite
ms.technology	database-engine
ms.tgt_pltfrm
ms.topic	language-reference
applies_to	SQL Server (starting with 2008)
f1_keywords	fn_cdc_is_bit_set sys.fn_cdc_is_bit_set_TSQL sys.fn_cdc_is_bit_set fn_cdc_is_bit_set_TSQL
dev_langs	TSQL
helpviewer_keywords	sys.fn_cdc_is_bit_set fn_cdc_is_bit_set
ms.assetid	792fe7cf-b3b8-4f25-8329-78d63f0e6921
caps.latest.revision	15
author	BYHAM
ms.author	rickbyh
manager	jhubbard

sys.fn_cdc_is_bit_set (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx_md]

Indicates whether a captured column has been updated by checking whether its ordinal position is set within a provided bitmask.

Applies to: [!INCLUDEssNoVersion] ([!INCLUDEssKatmai] through current version).

Transact-SQL Syntax Conventions

Syntax

  
sys.fn_cdc_is_bit_set ( position , update_mask )  

Arguments

position
Is the ordinal position in the mask to check. position is int.

update_mask
Is the mask identifying updated columns. update_mask is varbinary(128).

Return Type

bit

Remarks

This function is typically used as part of a change data query to indicate whether a column has changed. In this scenario, the function sys.fn_cdc_get_column_ordinal is used before the query to obtain the required column ordinal. sys.fn_cdc_is_bit_set is then applied to each row of change data that is returned, providing the column-specific information as part of the returned result set.

We recommend using this function instead of the function sys.fn_cdc_has_column_changed when determining whether columns have changed for all rows of a returned result set.

Permissions

Requires membership in the public role.

Examples

The following example uses sys.fn_cdc_is_bit_set to prepend to the result set generated by the query function cdc.fn_cdc_get_all_changes_HR_Department the column 'IsGroupNmUpdated' using the precomputed column ordinal and the value of __$update_mask as arguments to the call.

USE AdventureWorks2012;  
GO  
DECLARE @from_lsn binary(10), @to_lsn binary(10), @GroupNm_ordinal int;  
SET @from_lsn = sys.fn_cdc_get_min_lsn('HR_Department');  
SET @to_lsn = sys.fn_cdc_get_max_lsn();  
SET @GroupNm_ordinal = sys.fn_cdc_get_column_ordinal('HR_Department','GroupName');  
  
SELECT sys.fn_cdc_is_bit_set(@GroupNm_ordinal,__$update_mask) as 'IsGroupNmUpdated', *  
FROM cdc.fn_cdc_get_all_changes_HR_Department( @from_lsn, @to_lsn, 'all')  
WHERE __$operation = 4;  
GO  

See Also

Change Data Capture Functions (Transact-SQL)
sys.fn_cdc_get_column_ordinal (Transact-SQL)
sys.fn_cdc_has_column_changed (Transact-SQL)
cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL)
cdc.fn_cdc_get_net_changes_<capture_instance> (Transact-SQL)
About Change Data Capture (SQL Server)