MSSQL COLUMNS_UPDATED() function does not work as specified
You've probably read a bunch of stuff about using COLUMNS_UPDATED, and if you've ended up here, it's not working for you properly.
Here's a snippet from an APRESS book on using COLUMNS_UPDATED()
SELECT ColumnName = [column_name]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE [TABLE_NAME] = & -
TableName AND ORDINAL_POSITION = @bitmaskpower + 1
Note they get the column name from INFORMATION_SCHEMA.COLUMNS based on table name and ordinal position.
Now below is "Columns_Updated", "Ordinal Position", "Field Name", and the calculation of the position based on the columns_updated value for a table I was having trouble with.
Not only do the positions not match after the first column (primary key), but the offset is not fixed along the list of columns. I haven't found a solution yet, but I'm working on it.
0x010000000000 1 Membership ID - Right
0x080000000000 2 Address1 - OFF BY 2 (4)
0x800000000000 6 MembershipTypeID - OFF BY 2
0x000100000000 7 SubscriptionClassID - OFF BY 2
0x000200000000 8 AreaID - OFF BY 2
0x000800000000 10 Comment - OFF BY 2
0x004000000000 13 ZIP - OFF BY 2 (15)
0x000008000000 18 Subscription - OFF BY 2 (20)
0x000020000000 20 DateBilled - OFF BY 2 (22)
0x000040000000 21 Billed - OFF BY 2 (23)
0x000000020000 22 MobilePhone - OFF BY 4 (26)
0x000000080000 23 EmailAddress - OFF BY 5 (28)