MSSQL Bitwise Operations Using Two Varbinary Variables And COLUMNS_UPDATED()

While trying to write some triggers for MSSQL I stumbled upon this nice function called COLUMNS_UPDATED(). It returns a bitmask representing the columns that were modified during an update operation. It just so happened that this was exactly what I was looking for. I had a group of columns that I wanted the trigger to act upon, the rest I didn’t care about. So I went about creating a bitmask for my columns to & against COLUMNS_UPDATED(). Here is where I ran into two problems.

First, COLUMNS_UPDATED() returns a VARBINARY, so logically I also made mine a VARBINARY. Unfortunately, MSSQL does not support bitwise operations on two VARBINARY variables. One can be a VARBINARY, but the other must be an INT. This isn’t a problem if your table is 32 columns or less. Unfortunately that was not the case for the 74-column monster of a table I was working with. I searched around and it seemed like there really wasn’t a solution to this problem. So I wrote my own. It’s a stored procedure that will perform the bitwise operations, &, |, or ^ on two VARBINARY variables. It splits the VARBINARY variables into INT sized chunks and the performs the operations. I’m sure it could be more efficient, but it works.
binary_bitwise.sql

The second wonderful thing is the way that bits are organized. Withing each byte (8 bits) the least significant bit (rightmost) is the first column. So if you were interested in columns 1,3,4 you would get: 00001101. Then within your whole bitmask the most significant byte (leftmost) is the first 8 columns. So let’s say you were interested in columns 1,3,4,7,9,15,16 then you would get 01001101 11000001. Writing out the bits in order doesn’t make much sense to the human brain, notice columns 15 and 16 are in the middle. The easiest way I found is to write out 0s in byte sized groups then go through them backwards putting 1s in your columns.
Once you figure out your bitmask convert it to a hex number and you can use in the trigger. My monster table ended up with:

IF(dbo.binary_bitwise(COLUMNS_UPDATED(),0xFFF57851150404C02102,'&') != 0x0)

. Also remember to fill out all your bits. If you only have 12 columns, you still need 16 bits. Let’s say you are interested in column 2 and 11. 00000010 00000100 = 0x24.