An optimized version of function sys.fn_cdc_is_bit_set
Change Data Capture (CDC) table uses __$update_mask to track the modifications of ordinal columns.
__$update_mask’s type is varbinary(128)
The function sys.fn_cdc_is_bit_set is provided within Microsoft SQL Server. It is used to calculate if the value of a column is modified, based on the column’s position and the __$update_mask.
Its signature is:
sys.fn_cdc_is_bit_set(position, update_mask)
But the function sys.fn_cdc_is_bit_set provided in SQL Server is not optimal when there are a lot of data to process.
Here is an optimal version:
CREATE FUNCTION [dbo].[fn_cdc_is_bit_set_optimized]
(
@position INT,
@update_mask VARBINARY(128)
)
RETURNS BIT
AS
BEGIN
DECLARE @isSet BIT =
CASE WHEN SUBSTRING(@update_mask,DATALENGTH(@update_mask) - ((@position-1)/8),1) & POWER(2, (@position-1)%8) > 0
THEN 1 ELSE 0 END
RETURN @isSet;
END
