Understanding the SET_BIT() Function in SQL Server

by

SQL Server 2022 introduced the SET_BIT() function that sets a bit in an integer or binary value (other than a large object).

The function allows us to specify an offset for which to set a bit to 1 or 0.

Syntax

The function can be used in either of the following ways:

SET_BIT ( expression_value, bit_offset ) 
SET_BIT ( expression_value, bit_offset, bit_value )

The expression_value argument is the value that we’re setting the bit for, and bit_offset is the nth bit of the data to set in expression_value. The bit_offset is zero based (i.e. counting starts at 0).

By default, the bit is set to 1, but we also have the option of providing a bit_value as the third argument. If we do, it must be either 0 or 1.

Example

Here’s an example to demonstrate:

SELECT SET_BIT( 0x00, 0 );

Result:

0x01

To understand this result, we need to understand how SET_BIT() operates on the underlying binary value.

Here’s an explanation of the above result:

  1. First of all, the binary equivalent of 0x00 is 0000.
  2. We set the bit at an offset of 0. The offset is zero based, and so 0 is the first (rightmost, and least significant) bit.
  3. We didn’t specify a value to set, and therefore the default value of 1 was used.
  4. Therefore, the above example resulted in a binary value of 0001.
  5. The binary value is returned in its hexadecimal representation of 0x01.

Let’s increase the offset to 3:

SELECT SET_BIT( 0x00, 3 );

Result:

0x08

This time we get 0x08. That’s because the bit at offset 3 was set to 1 (remembering that 1 is the default value).

Therefore, the above operation resulted in a binary value of 1000, which translates into a hexadecimal value of 0x08.

Specifying a Value to Set

In the above examples I didn’t specify a value to set. I only provided the offset. Therefore, the default value of 1 was used.

Let’s specify a bit to set:

SELECT SET_BIT( 0x01, 0, 0 );

Result:

0x00

Here, I set the first bit to 0. In this case, it set the hexadecimal value to 0x00, because its original value was 0x01, which has a binary representation of 0001. This is because only the rightmost bit was set to 1, which was overwritten to 0, resulting in a binary value of 0000, which ultimately translates into a hexadecimal value of 0x00.

Here’s what happens if I apply the same offset and bit to a different value:

SELECT SET_BIT( 0x02, 0, 0 );

Result:

0x02

In this case, nothing changed. This is because the original value has a binary equivalent of 0010. I set the rightmost bit to zero, but the rightmost bit was already zero. Hence, no change.

Here’s another example:

SELECT SET_BIT( 0x03, 0, 0 );

Result:

0x02

In this case, the original value of 0x03 has a binary equivalent of 0011. So by setting the rightmost bit to zero, we end up with 0010, which translates to 0x02.

Let’s compare the default value with the various valid values that we can set the bit to:

SELECT 
  SET_BIT( 0x04, 0 ) AS "Default",
  SET_BIT( 0x04, 0, 1 ) AS "1",
  SET_BIT( 0x04, 0, 0 ) AS "0";

Result:

Default 1    0   
------- ---- ----
0x05    0x05 0x04

Out of Range Offset

The offset amount can’t be negative or greater than the last bit in the data type.

SELECT SET_BIT( 0x03, -1 );

Result:

Msg 9838, Level 16, State 3, Server 39ff0bbc9cf4, Line 1
Parameter 2 in function 'set_bit' is out of range 0 to 7.

And we get the same error when it’s greater than the last bit in the data type:

SELECT SET_BIT( 0x03, 8 );

Result:

Msg 9838, Level 16, State 3, Server 0f07a688e2df, Line 1
Parameter 2 in function 'set_bit' is out of range 0 to 7.

This will change depending on the data type. For example, running the same offset on the following value works without error:

SELECT SET_BIT( 0x4404, 8 );

Result:

0x4504