Dealing with the Bits of a Binary Value in SQL Server

In this article, I want to talk about a topic that you may never need. The only time I have really had need to look at the bits in a byte pattern. In (what is now) ancient versions of SQL Server didn't have such self-describing columns like in its metadata objects like it does now.

How you used to do it

For example, say you want to find all the read-write database using SQL Server 2022 system objects (I think this started back in 2005, in fact). You can write:

This will return those three system databases (because they still will always be read-write.) Using the old sysdatabases object, there was just a status column.

On my computer, and most likely yours, the output from this query is:

name           status
-------------- -----------
master         65544
msdb           65544
tempdb         65544

I expect that 99% of the people reading this probably would expect there to be a status table that contained the values of status. Seeing that this is a base 2 number, you may be in that 1% that thinks this might be a bitmask. but unless you have and eidetic memory, you probably don’t know what all of the bits mean.

A bitmask is a type of denormalization of values where instead of having a set of columns that have on or off values (no Null values), you encode it like:

00000101

Now the user has to figure out what bit each of the 8 bits are in the integer/binary value to determine a value. For status, you can determine if a database is read-only using the 10th place. In versions of SQL Server before 2022, this was achieved by doing something like this:

The idea is that if the 10th bit in the number is set to 1, that it AND (&) the value will return 1024 (since it will return 1024 (which is POWER(2,10)), and if it is value, it will return 0. (For the full list of bit values, check here: https://learn.microsoft.com/en-us/sql/relational-databases/system-compatibility-views/sys-sysdatabases-transact-sql?view=sql-server-ver16)

To see one that does return a match, check out the following 2 queries:

Both return three rows. Ok, so that is the basics, now lets look at some new syntax that makes this easier. For example, let’s take that 65536 value. It is 2 to the 16th power:

In SQL Server 2022, there are 5 new bitwise functions that have some value. For example, finding that a bit is set is a lot easier. The functions are:

  • GET_BIT – See if a bit is set in a binary value
  • SET_BIT – Set a bit in a binary value and return the new value
  • LEFT_SHIFT, RIGHT_SHIFT – Shift all the bits left or right in a binary value
  • BIT_COUNT – count the number of bits set in a value

In our previous query of status and checksum, you could rewrite as:

This returns:

name         is_readonly is_checksum
------------ -----------  -----------
master                 0            1
msdb                   0            1
tempdb                 0            1

While I am STILL never in my life going to bless a bitwise solution in the year 2022 (much less the year 2000), this does make it far less unpleasant to work with.

The rest of the bit functions are less useful in any solution I have ever seen, but they are interesting. For example:

This returns:

-------------
0x00000400

Which is binary for 1024 (cast that value to int and you will see it). Note that while bits may be technically implemented in SQL Server internally as bits from the left, the bit operators treat values as if they were from the right (since that maps to our typical numbering system). Now let’s set bits 1, 2, 3, and 4, then shift those bits right:

This outputs 30.. which may confuse you (it did me!), there is a bit 0 also. So it is 2 + 4 + 8 + 16 = 30

Shifting 30 right 1 position:

This outputs 15, which is now 1 + 2 + 4 + 8. Let’s shift it again:

This is analogous to RIGHT_SHIFT(RIGHT_SHIFT(30,1),1) and returns 7. Bits fall off to the side, so, for example:

Returns 0, because the first set of RIGHT_SHIFT statements pushed the bits al the way off of the map. Swap the calls:

And wait… it still falls off and returns 0. Why? Because this is technically a 31-bit integer (the sign takes off a bit), so you lose the bits anyhow. So be careful bit shifting!

Finally, let’s look at the BIT_COUNT function. Using the 30 value, which we know is 2 + 4 + 8 + 16. Next, execute the following:

And you will see that it returns 4, telling us there are 4 bits that are 1.

Might be the bit of information you absolutely never have a valid use for, but the more you know, you may some day actually need it!