New SQL Server 2022 Bitwise Operators with Practical Examples

By: Jeffrey Yao | Updated: 2023-01-26 | Comments | Related: > SQL Server 2022

Problem

In SQL Server, there are a few times the business data model or rule is at a bit-level. For example, the interpretation of the columns_updated() value is at a bit-level. Or the values of some columns (freq_type, freq_relative_interval, etc.) in msdb.dbo.sysschedules. Before SQL Server 2022, to detect and operate on these values was pretty inconvenient. With SQL Server 2022, new bitwise operators have been introduced and we have a more elegant solution to identify these values, such as how to identify all updated columns from an UPDATE statement.

Solution

I wrote a previous tip about identifying the updated columns in an update, however, SQL Server 2022 has more elegant solutions based on the new bitwise operators. There are the two new bitwise operators used in this tip:

RIGHT_SHIFT takes two parameters and returns the first parameter bit-shifted right by the number of bits specified in the second parameter. Its syntax format is one of these two:

RIGHT_SHIFT ( expression_value, shift_amount )

-- or this
expression_value >> shift_amount

GET_BIT takes two parameters and returns the bit in expression_value that is in the offset defined by bit_offset. Its syntax format is:

GET_BIT ( expression_value, bit_offset)

Sample Use Case

The problem we want to solve is to create a table trigger for UPDATEs, so the trigger will print out all the updated columns when the table is updated.

Inside the trigger, we will use columns_updated() to capture the information of the updated columns, scan the value of columns_updated() byte by byte, then use get_bit() to find the value of each bit of the byte and log the information, i.e., the bit position and the bit value, into a table variable. At the very end, print out the list of the updated columns by joining the table variable with the sys.columns view.

One key point regarding the columns_updated() value and the corresponding columns is that the rightmost bit of the leftmost byte represents the first column in the table; the next bit to the left represents the second column, and so on, as illustrated below. The 8 bits in the 1st byte (i.e., the leftmost byte) represent the first 8 columns, the 2nd byte (i.e., the 2nd leftmost byte) represents the next 8 columns (if applicable), and so on. If the bit value is 1, it means this column is updated in the UPDATE statement.

Source Code

We first create a test table with multiple columns, as shown below, and then insert two records:

use MSSQLTIPS;
drop table if exists dbo.t;
create table dbo.t ( 
  c1 int primary key, c2 int, c3 int, c4 int, c5 int
, c6 int, c7 int, c8 int, c9 int, c10 int
, c11 int, c12 int, c13 int, c14 int, c15 int
, c16 int, c17 int, c18 int, c19 int, c20 int
, c21 int, c22 int, c23 int, c24 int, c25 int
, c26 int, c27 int, c28 int, c29 int, c30 int
, c32 int, c33 int, c34 int, c35 int, c36 int);
go

-- insert two records
insert into dbo.t values (
1,2,3,4,5,6,7,8,9,10, 
11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,27,28,29,30,
31,32,33,34,35),
(2,102,103,104,105,106,107,108,109,110, 
11,112,113,114,115,116,117,118,119,120,
21,122,123,124,125,126,127,128,129,130,
31,132,133,134,135);

Next, we create a trigger on this table. The trigger will display updated columns whenever there is an update transaction on this table.

-- this trigger is to display the updated columns
create trigger trg_upd_dbot on dbo.t for update as
begin
 set nocount on;
 
 declare @column_updated varbinary(128), @updated_columns_count int,@byte_cnt int=1, @cnt int=0;
 declare @byte binary(1);
 declare @byte_len int;
 declare @col_str varchar(max);
 declare @ct table (column_id int identity, isSet bit);
 
 set @column_updated = columns_updated();
 
 print cast(bit_count(columns_updated()) as varchar) + ' columns have been updated';
 
 select @updated_columns_count = bit_count(@column_updated), @byte_len = DATALENGTH(@column_updated);
 
 print cast(bit_count(columns_updated()) as varchar) + ' columns have been updated';
 
 while (@byte_cnt <= @byte_len and @cnt < @updated_columns_count)
 begin -- first while
    set @byte = SUBSTRING(@column_updated, @byte_cnt,1);
    
    insert into @ct (isSet) values (get_bit(@byte, 0)),(get_bit(@byte, 1)),(get_bit(@byte, 2))
    ,(get_bit(@byte, 3)),(get_bit(@byte, 4)),(get_bit(@byte, 5))
    ,(get_bit(@byte, 6)),(get_bit(@byte, 7));
    
    set @cnt += 1;
    set @byte = right_shift(@byte,1);
    
    select @cnt = count(*) from @ct where isSet = 1;
    
    set @byte_cnt +=1;
 end -- first while
 
 select c.column_id, [col_name]=c.name, ct.isSet
 from @ct ct
 inner join sys.columns c
 on c.column_id = ct.column_id
 inner join sys.objects o
 on o.parent_object_id = c.object_id
 and o.object_id = @@PROCID
 where ct.isSet = 1;
 
 select @col_str = STRING_AGG(c.name, ';')
 from sys.columns c
 inner join @ct t
 on c.column_id = t.column_id
 and t.isSet = 1
 inner join sys.objects o
 on o.parent_object_id = c.object_id
 and o.object_id = @@PROCID;
 print 'updated columns = ' + @col_str;
end -- trigger

Now, if we run the following update in an SSMS query window, we will get a list of updated columns:

update t set c2=1, c4=100, c5=150, c20=200, c27=270,c35=235
from dbo.t as t
where c1 = 1;

So we can see that with new bitwise operators in SQL Server 2022, it is much easier to decode the columns_updated() value and find out the exact columns that are updated, especially for a wide table, i.e., a table with many columns.

Summary

Using triggers to monitor updates on critical columns (or a combination of columns) and then responding accordingly is still a relatively common practice. Knowing what columns are updated for wide tables is important for implementing business rules.

In this tip, we discussed how to "translate" columns_updated() value into the real column names with new features introduced in SQL Server 2022.

Before SQL Server 2022, such an effort was cumbersome and involved much coding. But now, with new bitwise operators, it is simple and straightforward.

Next Steps

Several good tips on MSSQLTips.com talk about columns_updated() and its usage. Please take a look at the listed links below:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2023-01-26

Find Updated Columns in Update Transaction with New BitWise Operators in SQL Server 2022