sqlsql-serverbitmaskchange-tracking

How to implement CHANGE_TRACKING_IS_COLUMN_IN_MASK in my application?


When working with Change Tracking in SQL Server, you're supposed to use CHANGE_TRACKING_IS_COLUMN_IN_MASK to determine which column was changed when dealing with updates. For example, like so:

DECLARE @last_synchronization_version bigint = ...;
DECLARE @column_id int = ...;

-- The statement below returns 1 if the specified column (@column_id) was changed, otherwise 0.

SELECT CHANGE_TRACKING_IS_COLUMN_IN_MASK(@column_id, SYS_CHANGE_COLUMNS)
FROM CHANGETABLE(CHANGES dbo.MyTable, @last_synchronization_version) AS CT

I wonder, is there a way to implement CHANGE_TRACKING_IS_COLUMN_IN_MASK myself, so I can work with the value of SYS_CHANGE_COLUMNS in my application without having to know beforehand which columns my application is interested in when executing the query?

For example, when I only change the value of the column with ID 11, the value of SYS_CHANGE_COLUMNS is 0x000000000B000000.

enter image description here

How can I programmatically determine that this mask contains the information that column 11 was changed?


Solution

  • It turns out SYS_CHANGE_COLUMNS consists of a byte array that can be grouped into groups of 4 bytes. The more columns were changed, the longer the byte array will be, thus the more groups you can make. The first byte of each group represents an ID of a column that was changed. In all of my tests, the other 3 bytes of each group were empty (0). I assume these bytes will be used when you have a column ID larger than 255. It seems the order in which columns were changed determines the order in which they appear in the byte array. Also, the first group of 4 bytes will always be empty (0), I'm not sure why.

    To use this in application code, all you need to do is get a mapping for each column name and it's respective column ID. The previous paragraph should explain how to use SYS_CHANGE_COLUMNS to determine which column ID appears in the byte array.

    C# example:

    public static IEnumerable<int> GetColumnIdsInMask(byte[] columns)
    {
        // TODO: deal with column IDs larger than 255
        for (var i = 4; i < columns.Length; i += 4)
        {
            yield return columns[i];
        }
    }
    
    public static bool IsColumnInMask(int columnId, byte[] columns)
    {
        return GetColumnIdsInMask(columns).Any(x => x == columnId);
    }