New Bit Manipulation Functions in SQL Server 2022

The need to manipulate data at the bit level with bitwise operations isn’t common in T-SQL, but you might stumble into such a need in some specialized scenarios. Some implementations store a set of flags (yes/no, on/off, true/false) in a single integer or binary-typed column, where each bit represents a different flag. One example is using a bitwise representation of a set of user/role permissions. Another example is using a bitwise representation of a set of settings turned on or off in a given environment. Even SQL Server stores some flag-based data using bitwise representation. Examples include the following:

• The set_options and required_cursor_options cached plan attributes, which you obtain using the sys.dm_exec_plan_attributes DMF
• Querying the current session’s state of session options with the @@OPTIONS function
• Setting the server configuration option “user options” using bitwise representation
• The COLUMNS_UPDATED trigger-related function, which indicates which columns were inserted or updated
• The GROUPING_ID function, which constructs a bitwise representation of the current grouping set

Bit manipulation may involve checking whether a certain flag is on or off, setting a certain flag on or off, checking which flags are on, counting how many flags are on, shifting bits left or right, and more.

Prior to SQL Server 2022, T-SQL had minimal support for bit manipulation. It had support for the following operators:

• & (bitwise AND)
• | (bitwise OR)
• ^ (bitwise XOR)
• ~ (bitwise NOT)

You could achieve some of the aforementioned bit manipulation needs using a combination of these operators and some mathematics. But the available manipulation was limited and often resulted in complicated logic.

SQL Server 2022 introduces a set of new bit manipulation functions and operators designed to enhance your bit manipulation abilities, and it also makes some of the tasks just easier and more intuitive to achieve. The new functions and operators are as follows:

• GET_BIT function
• SET_BIT function
• BIT_COUNT function
• LEFT_SHIFT function / << operator
• RIGHT_SHIFT function / >> operator

I’ll provide a brief description of the functions. For the full technical details, I provided links to the official documentation. In this article, I want to focus more on how these functions improve and simplify tasks that were harder to achieve in the past.

GET_BIT and SET_BIT

The GET_BIT function extracts a bit from an input value in a specified bit offset. This function has the following syntax:

The input value can be of an integer or a binary non-large object type. The bit offset is 0-based, representing the offset of the bit from right to left. So you specify 0 for the first (rightmost) bit, 1 for the second, …, 31 for the thirty-second, and so on. The returned value is of the BIT type. It’s 1 if the requested bit is set and 0 if it’s not set.

For example, the ARITHABORT session option is represented by bit offset 6 in the bitmap returned by the @@options function. Use the following code to check the status of this option in your session:

SELECT GET_BIT( @@OPTIONS, 6 );

The ARITHABORT option was enabled in the session where I ran this code, so I got the following output:

-----
1

The SET_BIT function returns a manipulated input value, where the bit in the specified bit offset is set to 1 by default or to the specified bit state (1 or 0) if indicated. This function has the following syntax:

The supported types for inputval and bitoffset are the same as for the GET_BIT function. The bitstate input can be of a bit or integer type, but it must be 1 or 0.

For example, the following code returns both the current state of @@OPTIONS as well as a manipulated state with ARITHABORT set to off:

SELECT @@OPTIONS AS cursession, SET_BIT( @@OPTIONS, 6, 0 ) AS cursession_ARITHABORT_off;

I get the following out:

cursession  cursession_ARITHABORT_off
----------- -------------------------
5496        5432

In case it wasn’t obvious, it’s important to note using the SET_BIT function doesn’t change the input value in any way; rather, it just returns a manipulated value based on the input.

BIT_COUNT

The BIT_COUNT function counts the number of bits set in the input value. This function has the following syntax:

The input value can be of an integer or a binary non-large object type. The output is of a BIGINT type.

For example, the following code counts the number of session options set in the current session:

SELECT BIT_COUNT( @@OPTIONS );

This code generates the following output in my session:

--------------------
7

LEFT_SHIFT and RIGHT_SHIFT

The LEFT_SHIFT function, also available as the << operator, shifts the bits of the input value the requested number of bits to the left, filling vacant bits with 0s. The function and operator have the following syntax:

Like with the other bit manipulation functions, the input value can be of an integer or a non-large-object binary type. The output type is the same as the input. The numbits input is of an integer type.

The RIGHT_SHIFT function, also available as the >> operator, shifts the bits of the input value the requested number of bits to the right, filling vacant bits with 0s. Note this is what’s known as a logical right shift as opposed to another kind called arithmetic right shift, where vacant bits replicate the original value of the most significant bit (MSB). T-SQL doesn’t support an arithmetic right shift. The function and operator have the following syntax:

With both functions, bits that overspill (original numbits leftmost bits in a left shift and original numbits rightmost bits in a right shift) are discarded. If you provide a negative integer as numbits, SQL Server will convert it to the counterpart function with the positive value. So x >> -1 is converted to x << 1.

The following example shifts the bits in the input value 10 one bit to the right and one bit to the left:

SELECT 10 >> 1 AS rightshift, 10 << 1 AS leftshift;

This code generates the following output:

rightshift  leftshift
----------- -----------
5           20

The bitwise representation of the INT-value 10 is as follows:

0000 0000 0000 0000 0000 0000 0000 1010

Shifting one bit to the right results in the following:

0000 0000 0000 0000 0000 0000 0000 0101

This is the integer value 5.

Shifting one bit to the left results in the following:

0000 0000 0000 0000 0000 0000 0001 0100

This is the integer value 20.

Logical bit shifting is used in some computing environments as an efficient low-level way to achieve division or multiplication of unsigned integers by powers of two. For instance, right shifting val by n bits achieves val/2^n, and left shifting val by n bits achieves val*2^n.

Obviously, you won’t use bit shifting in T-SQL for such purposes. More likely use cases would be shifting existing bitwise representations of sequences of flags due to the introduction of new flags and the need to use the rightmost bits for the new flags. For instance, suppose you store user and role permission information as a sequence of bits in a BINARY(25) column called perms in a table called dbo.UserRolePerms. The column has a capacity to represent up to 200 permissions, but currently, the 80 rightmost bits (offsets 0 through 79) are used to represent 80 permissions. At some point in the application’s life, 10 new permissions are introduced. For whatever reason, a decision is made to use the 10 rightmost bits (offset 0 through 9) for the new permissions. This means you need to shift the existing permission info 10 bits to the left. The shifting part can be achieved with the following update:

UPDATE dbo.UserRolePerms
SET perms = perms << 10;

Handling Bit Manipulation Tasks Is Now Easier and More Intuitive

To get a real appreciation for the benefits of the new bit manipulation functions, all you need to do is try and handle similar tasks without them.

Extracting the State of a Bit in a Given Offset or Setting It

Given an input value @val and bit offset @offset, write an expression designed to extract the state of the requested bit without the new functions. The technique you use to address this task depends on the data type of @val and on whether certain assurances are provided.

The TINYINT datatype in SQL Server is an unsigned integer, and SMALLINT, INT, and BIGINT are signed integers represented using the two’s complement notation. If @val is of the TINYINT type—or of the SMALLINT, INT, or BIGINT types with the assurance the leftmost bit isn’t used—you can extract the bit value (a value with only the specified bit offset set to the same state like in the input @val) with the expression @val & POWER(2, @offset) and the bit state (1 or 0) with the expression SIGN(@val & POWER(2, @offset)). Here’s an example:

DECLARE @val AS INT = 5496, @offset AS INT = 6;

SELECT
  @val & POWER(2, @offset) AS bitval,
  SIGN(@val & POWER(2, @offset)) AS bitstate;

This code returns the following output on my system:

bitval      bitstate
----------- -----------
64          1

But what if you’re dealing with an implementation using a signed integer type for @val and the leftmost bit? Both of the following computations are simply incorrect for the leftmost bit, not to speak of the fact the code fails with an overflow error:

DECLARE @val AS INT = 5496, @offset AS INT = 31;

SELECT
  @val & POWER(2, @offset) AS bitval,
  SIGN(@val & POWER(2, @offset)) AS bitstate;

Yes, you could add logic to handle the special case differently, but this adds complexity to your code.

And what about a binary input? What if @val is of a BINARY(25) type? Now, coming up with a solution is far more complicated (try it!).

Using the new features, it’s a piece of cake. To get the bit state, use the GET_BIT function with the inputs @val and @offset with no special manipulation. Here’s an example with an INT-typed value as input:

DECLARE @val AS INT = 5496, @offset AS INT = 31;

SELECT GET_BIT( @val, @offset );

This code generates the following output, indicating the bit in the specified offset is off:

-----
0

Here’s an example with a BINARY(25) input:

DECLARE @val AS BINARY(25) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037, 
        @offset AS INT = 100;

SELECT GET_BIT( @val, @offset );

This code generates the following output, indicating the bit in the specified offset is on:

-----
1

To get the bit value, you can use the SET_BIT function, with the first input with all bits zeroed, the second input being @@offset, and the third input being the state returned by GET_BIT. Here’s an example with an integer input:

DECLARE @val AS INT = 5496, @offset AS INT = 6;

SELECT SET_BIT(0, @offset, GET_BIT( @val, @offset ));

This code returns the following output:

-----------
64

And here’s an example with a BINARY(25) input:

DECLARE @val AS BINARY(25) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037, 
        @offset AS INT = 100;

SELECT SET_BIT(CAST(0x AS BINARY(25)), @offset, GET_BIT( @val, @offset ));

This code generates the following output:

----------------------------------------------------
0x00000000000000000000000010000000000000000000000000

You will naturally face similar complexities when trying to set a bit in a requested position. Try it with the special cases and binary inputs! Instead, starting with SQL Server 2022 you simply use the SET_BIT function.

Counting Bits

How about counting how many bits are set in an input value? Again, if it’s a signed integer and you have an assurance the leftmost bit isn’t used, you can use a table of numbers, a bitwise AND (&) operator, and a COUNT aggregate.

Here’s code to create the GetNums function, which returns a sequence of integers in the requested range:

CREATE FUNCTION dbo.GetNums(@low AS BIGINT = 1, @high AS BIGINT)
RETURNS TABLE
AS
RETURN
WITH
  L0 AS ( SELECT 1 AS c 
          FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),
                      (1),(1),(1),(1),(1),(1),(1),(1)) AS D(c) ),
  L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
  L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
  L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
  Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L3 )
SELECT TOP(@high - @low + 1)
   rownum AS rn,
   @high + 1 - rownum AS op,
   @low - 1 + rownum AS n
FROM Nums
ORDER BY rownum;
GO

And here’s code to count the bits set in an INT-typed input stored in @val, assuming the leftmost bit isn’t used:

DECLARE @val AS INT = 5496;

SELECT COUNT(*) AS bitcount
FROM dbo.GetNums(0, 30) AS N
WHERE @val & POWER(2, N.n) <> 0;

This code generates the following output:

bitcount
-----------
7

Clearly, this isn’t a cheap calculation. Moreover, if you do use the leftmost bit or the input is a binary-typed one, the solution becomes more complex (try it!). With the new features in SQL Server 2022, you simply use the BIT_COUNT function:

DECLARE @intval AS INT = 5496, 
        @binval AS BINARY(25) = 0x9B3B9D5AD3084AB1A522082491B8519C008AB4AACB7064B037;

SELECT BIT_COUNT(@intval) AS cntintval, BIT_COUNT(@binval) AS cntbinval;

This code generates the following output:

cntintval            cntbinval
-------------------- --------------------
7                    85

Bit Shifting

How about bit shifting? Suppose you’re given the inputs @val and @offset and you need to right shift or left shift the bits in @val based on the offset in @offset.

For right shifting, if @val is a nonnegative value of a signed int type such as INT, you can use the expression @val / POWER(2, @offset).

For left shifting, if @val is a nonnegative value of a signed int type such as INT and you have an assurance the @offset + 1 leftmost bits are currently unused (are 0), you can use the expression @val * POWER(2, @offset). In all other cases, including when the above assurances aren’t provided or when @val is of a binary type, you need to create more complex solutions (try it!).

With the new functions and operators, you simply provide the inputs with no manipulation:

DECLARE @val AS INT = -1, @offset AS INT = 1;

SELECT @val >> @offset AS rightshift, @val << @offset AS leftshift;

This code generates the following output:

rightshift  leftshift
----------- -----------
2147483647  -2

The INT-typed value -1 has the following bitwise representation:

1111 1111 1111 1111 1111 1111 1111 1111

Right shifting by one bit results in the following:

0111 1111 1111 1111 1111 1111 1111 1111

This is the integer value 2147483647.

Left shifting by one bit results in the following:

1111 1111 1111 1111 1111 1111 1111 1110

This is the integer value -2.

Conclusion

Bit manipulation isn’t a common need in T-SQL, but it’s relevant for some built-in features in SQL Server and could be relevant in some user-defined implementations. Prior to SQL Server 2022, T-SQL had limited bit manipulation capabilities, requiring you to write complex code with mathematical acrobatics in some cases. SQL Server 2022 introduces five new bit manipulation functions and two alternative operators, enabling simpler and more intuitive manipulation.

What’s still missing in T-SQL in this space are aggregate bitwise functions performing aggregate bitwise AND, OR, and XOR operations. Oracle, for instance, supports such functions. As an example use case for such functions, suppose a user is a member of several roles and the permissions of each role are stored using a bitwise representation similar to the example I used in the article. The effective permissions of the user are the aggregate bitwise OR of the permission bitmaps of all roles the user is a member of.

At any rate, it’s good to see how T-SQL keeps evolving and adding more functionality.

Happy bitwising!