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:
1 2 3 4 |
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.
1 2 3 |
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 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:
1 2 3 |
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:
1 2 3 4 5 6 7 8 9 |
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:
1 |
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:
1 2 3 4 |
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:
1 2 3 4 5 |
DECLARE @value VARBINARY(64) = 0; SET @value = SET_BIT(@value,10); SELECT @value |
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:
1 |
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:
1 |
SELECT RIGHT_SHIFT(30,1); |
This outputs 15, which is now 1 + 2 + 4 + 8. Let’s shift it again:
1 |
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:
1 |
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:
1 |
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:
1 |
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!
Load comments