mssql cdc update_mask filter changes made only in column TS

Question

I want to find the rows in my mssql cdc table where only the column "TS" has been changed.

So I found some logic to check if a specific column was changed (this works), but I need to check if only the column TS was changed:

SET @colorder = sys.fn_cdc_get_column_ordinal('dbo_mytable', 'TS')

SELECT case when substring([__$update_mask],len([__$update_mask]) - ((@colorder-1)/8),1) & power(2,(@colorder-1)%8) > 0 then 1 else 0 end

FROM cdc.fn_cdc_get_all_changes_dbo_MYTABLE(@from_lsn, @to_lsn, 'all') PD

Answer 1

I've been meaning to write functions like this for a while, thanks for giving me a reason to actually do it.

Please do some unit testing of your own, I have only done a few very basic checks

-- inline tabular function because it's more versatile
-- cross applies used purely for ease of reading, 
-- could just make nested calls but hard to read. Up to you.
-- pass null to flip, otherwise pass explicit value you want the bit to be set to
create function dbo.setbit(@b varbinary(128), @bitpos tinyint, @value bit = null) 
returns table as
return
(
    select      result = cast(result.val as varbinary(128))
    from        (select len(@b) - ((@bitpos - 1) / 8)) bytepos(val)
    cross apply (select substring(@b, bytepos.val, 1)) byte(val)
    cross apply (select power(2, (@bitpos - 1) % 8)) mask(val)
    cross apply (
                   select  cast
                           (
                               case @value
                                   when 1 then byte.val | mask.val
                                   when 0 then byte.val & ~mask.val
                                   else byte.val ^ mask.val
                               end
                               as binary(1)
                           )
                ) newbyte(val)
    cross apply (select stuff(@b, bytepos.val, 1, newbyte.val)) result(val)
);

-- scalar wrapper for tabular function
create function dbo.setbitscalar(@b varbinary(128), @bitpos tinyint, @value bit = null) 
returns varbinary(128) as
begin
    return (select result from dbo.setbit(@b, @bitpos, @value));
end

-- how it works
declare @b varbinary(128) = 0x0101 -- 2 bytes!
select 
    dbo.setbitscalar(@b, 1, 1),        -- set bit 1 to 1
    dbo.setbitscalar(@b, 1, 0),        -- set bit 1 to 0
    dbo.setbitscalar(@b, 1, default)   -- flip bit 1
    
-- how to use it in your case:
-- set the @colorder bit in the mask to zero, 
-- then see if the resulting mask is zero
-- if it is, then only TS changed
SET @colorder = sys.fn_cdc_get_column_ordinal('dbo_mytable', 'TS')

select      only_TS_changed = iif(t.result = 0x, 1, 0) 
from        cdc.fn_cdc_get_all_changes_dbo_MYTABLE(@from_lsn, @to_lsn, 'all') PD
cross apply dbo.setbit(PD.[__$update_mask], @colorder, 0) t