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
.
How can I programmatically determine that this mask contains the information that column 11 was changed?
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);
}