Update triggers in SQL Server frequently involve tests to determine which columns were updated. In this article we'll walk through my own exploration of using the built-in COLUMNS_UPDATED() function. I was curious to see how it compared with other, perhaps more traditional methods of testing columns within an update trigger.

COLUMNS_UPDATED() Returns a Bit Pattern

When a trigger fires, Columns_Updated() returns a varbinary value indicating which columns were updated. This is a bit pattern, with the number of bits corresponding to the number of columns in the table. Starting from column 1, the COLUMNS_UPDATED() bit pattern has a 1 for "column updated" and 0 for "not updated".

This is what it is supposed to look like: 01010011… Since tables can have 1024 columns, how can we return 1024 bits? The answer, for any number of bits, is simple and elegant - as 8-bit bytes. Each byte can be represented as an ASCII character – as shown in the test query.

What does that varbinary value returned by Columns_Updated() look like, I wondered. How do I see it for myself? Capture it red-handed, in the act?  How would I verify/cross-check/debug a fleeting thing?

To test this, I created two tables: a vendorMasterTest, which is a copy of Dynamics GP's "PM00200" (Vendor Master), and a table called TriggerTester to capture the Columns_Updated() result. The details of those tables aren't important, but here's the actual trigger.

CREATE TRIGGER [dbo].[VendorMasterTest_changes]       
ON [dbo].[VendorMasterTest]
AFTER UPDATE
AS
BEGIN    
SET NOCOUNT ON;
INSERT dbo.TriggerTester 
(ColumnsUpdated )        
VALUES        
(columns_updated())
END

An insert and three updates later, here's what was logged. (the insert, as expected, had no effect here)

select  RowID
, COLUMNSUPDATED
, len(ColumnsUpdated) as length
, cast(ColumnsUpdated as varchar(2000)) as ASCII
FROM  dbo.TriggerTester

Using COLUMNS_UPDATED(): Bytes and Bit-masks

As mentioned above, in order to work with the bit pattern returned by COLUMNS_UPDATED(), you have to deal with each byte separately. The first byte represents table columns 1 – 8; the second is for columns 9 – 16, and so forth.

A particular byte is extracted using the SUBSTRING function.

SUBSTRING(COLUMNS_UPDATED(),1,1) -- Byte 1; cols 1-8
SUBSTRING(COLUMNS_UPDATED(),4,1) -- Byte 4; cols 25-32

Similarly, you must compute the bitmask of the columns you are evaluating. Starting with column 1, mark a 1 for the column to evaluate, 0 to skip. That is your bitmask. Remember, each set of columns is represented by a byte. Next, group the bits into 8-bit bytes and convert it to an integer. The columns you're interested in might all be part of the same byte, or they could belong to different bytes.

Excel's BIN2DEC can be used to get the bit mask as in the image here:

Or with a query:

SELECT TABLE_NAME
, COLUMN_NAME
,((COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')-1) /8)+1  AS ByteNum
,POWER(2,(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')-1)%8)  AS bitVal
FROM INFORMATION_SCHEMA.COLUMNS C
where C.TABLE_NAME = 'PM00200' 
and C.COLUMN_NAME in ('VENDNAME','VNDCHKNM','VADDCDPR','VADCDTRO','TXIDNMBR','TEN99TYPE')
ORDER BY 3,4

For a given column name, this query shows the corresponding COLUMN_UPDATED byte-number and the column's positional integer value.   Each individual bit's value corresponds to a particular power of 2.

Here are the results for a small set of columns (the column-names are part of the query). Four columns are represented in byte #1; the other two are both in byte #4.

For byte #1, the bit values (powers of two) for our set of columns combine to yield a bit mask of "10010110". Using the query above, here the values are summed by the byte#.

SELECT X.ByteNum
, SUM(X.bitVal)  AS bitval
FROM(
SELECT TABLE_NAME
, COLUMN_NAME
,((COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')-1) /8)+1  AS ByteNum
,POWER(2,(COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), COLUMN_NAME, 'ColumnID')-1)%8)  AS bitVal
FROM INFORMATION_SCHEMA.COLUMNS C
where C.TABLE_NAME = 'PM00200' and
C.COLUMN_NAME in ('VENDNAME','VNDCHKNM','VADDCDPR','VADCDTRO','TXIDNMBR','TEN99TYPE')
) AS X
GROUP BY X.ByteNum
ORDER BY X.ByteNum

Finally we can use those test values in our trigger. Formulating the tests in T-SQL requires an understanding of Bitwise operators – AND, OR, XOR, NOT. I won't go into that here, but here are just a few examples:

Remember that we're only evaluating the bitmask for  the columns we care about.

-- Any column: at least one of our byte-1 or byte-4 columns was updated:
SUBSTRING(COLUMNS_UPDATED(),1,1)  & 150 > 0 OR  -- at least one column in byte-1, OR
SUBSTRING(COLUMNS_UPDATED(),4,1)  & 130 > 0      -- at least one column in byte-4
-- All columns: every one of our byte-1 and byte-4 columns was updated.
SUBSTRING(COLUMNS_UPDATED(),1,1)  & 150 = 150 AND        -- all columns in byte-1, AND
SUBSTRING(COLUMNS_UPDATED(),4,1)  & 130 = 130            -- all columns in byte-4
SUBSTRING(COLUMNS_UPDATED(),1,1)  & 150 = 4  --  VNDCHKNM updated
SUBSTRING(COLUMNS_UPDATED(), 1, 1) & (16 | 128) = (16 | 128) VADDCDPR or VADCDTRO was updated

Note: Updated does not necessarily mean changed. If your statement was

update table1 set column1 = column1

COLUMNS_UPDATED() would indicate that the column1 was updated. In other words, if the column was "set" then it was "updated".   If you need to need to know whether there was a "change", look in the INSERTED and DELETED tables.

Conclusion

It was frustrating and challenging to get to the bottom of this and to understand how it all works. It was also fun to solve a puzzle.

I’m not sure I’d use Columns_Updated(). The traditional way of coding the trigger seems to be perfectly adequate.

CREATE TRIGGER [dbo].[VENDOR_DELETES]
ON [dbo].[PM00200] AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (  update(VENDNAME)
or update(VNDCHKNM)
)

This is clean, easy to understand, and self-documenting. Why deal with the arcana of bit masks?  Imagine trying to figure this out a month later – I’d need a refresher on this topic. I’d be annoyed at myself for not taking the simpler route.

By the way (and some references)

SQL Server Documentation on using COLUMNS_UPDATED: https://docs.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-2017

• The ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is not compatible with the bit pattern of columns returned by COLUMNS_UPDATED.
• To obtain a bit pattern compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function

A Database Journal article discussing COLUMNS_UPDATED carries a warning: https://www.databasejournal.com/features/mssql/article.php/1479821/Using-ColumnsUpdated-in-a-Trigger.htm

• The biggest pitfall with this technique is that if the column order changes, the trigger will continue to function - just incorrectly.

In practice, if you want to change column-order or insert a new column somewhere in the middle of a table, the table must be dropped and re- created, which also involves dropping the trigger. However, SQL Server Management Studio permits rearranging the column order or adding new columns in the middle of a table. Behind the scenes, it drops and re-creates the table and re-creates your triggers (and other objects).   However unlikely, this vulnerability is worth remembering.