Dealing with the Bits of a Binary Value in SQL Server

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:

SELECT name
FROM sys.databases
WHERE is_read_only = 0
  AND name IN ('master','msdb','tempdb');

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.

SELECT name, status
FROM   sys.sysdatabases
WHERE  name IN ('master','msdb','tempdb');

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 looks at 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:

SELECT name, status & 1024 AS is_readonly
FROM   sys.sysdatabases
WHERE  name IN ('master','msdb','tempdb');

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:

SELECT name
FROM   sys.databases
WHERE  page_verify_option_desc = 'CHECKSUM'
  AND  name IN ('master','msdb','tempdb');

SELECT name
FROM   sys.sysdatabases
WHERE  status & 65536 = 65536 
 AND name IN ('master','msdb','tempdb');

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:

SELECT POWER(2,16); --returns 65536

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:

SELECT name, GET_BIT(status,10) AS is_readonly,
                         GET_BIT(status,16) AS is_checksum
FROM   sys.sysdatabases
WHERE  name IN ('master','msdb','tempdb');

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:

DECLARE @value VARBINARY(64) = 0;

SET @value = SET_BIT(@value,10);

SELECT @value AS INT[

This returns:

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

Which is binary for 1024. 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:

SELECT SET_BIT(SET_BIT(SET_BIT(SET_BIT(0,1),2),3),4);

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:

SELECT RIGHT_SHIFT(30,1);

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

SELECT RIGHT_SHIFT(30,2);

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

SELECT LEFT_SHIFT(RIGHT_SHIFT(30,100),100);

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

SELECT RIGHT_SHIFT(LEFT_SHIFT(30,100),100);

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:

SELECT BIT_COUNT(30);

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!

 

The post Dealing with the Bits of a Binary Value in SQL Server appeared first on Simple Talk.

from Simple Talk https://ift.tt/zMGTcbR
via