{"id":95170,"date":"2022-12-04T21:00:14","date_gmt":"2022-12-04T21:00:14","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95170"},"modified":"2022-12-05T09:47:35","modified_gmt":"2022-12-05T09:47:35","slug":"dealing-with-the-bits-of-a-binary-value-in-sql-server","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/dealing-with-the-bits-of-a-binary-value-in-sql-server\/","title":{"rendered":"Dealing with the Bits of a Binary Value in SQL Server"},"content":{"rendered":"

How you used to do it<\/h2>\n

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:<\/p>\n

SELECT name\r\nFROM sys.databases\r\nWHERE is_read_only = 0\r\n  AND name IN ('master','msdb','tempdb');<\/pre>\n
This will return those three system databases (because they still will always be read-write.) Using the old sysdatabases<\/code> object, there was just a status column.<\/p>\n
SELECT name, status\r\nFROM   sys.sysdatabases\r\nWHERE  name IN ('master','msdb','tempdb');<\/pre>\n
On my computer, and most likely yours, the output from this query is:<\/p>\n
name\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0status
\n-------------- -----------
\nmaster\u00a0 \u00a0 \u00a0 \u00a0 \u00a065544
\nmsdb\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a065544
\ntempdb\u00a0 \u00a0 \u00a0 \u00a0 \u00a065544<\/code><\/p>\n
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.<\/p>\n
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:<\/p>\n
00000101<\/code><\/p>\n
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:<\/p>\n
SELECT name, status & 1024 AS is_readonly\r\nFROM   sys.sysdatabases\r\nWHERE  name IN ('master','msdb','tempdb');<\/pre>\n
The idea is that if the 10th bit in the number is set to 1, that it AND<\/code> (&<\/code>) the value will return 1024<\/code> (since it will return 1024<\/code> (which is POWER(2,10))<\/code>, 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<\/a>)<\/p>\n
To see one that does return a match, check out the following 2 queries:<\/p>\n
SELECT name\r\nFROM   sys.databases\r\nWHERE  page_verify_option_desc = 'CHECKSUM'\r\n  AND  name IN ('master','msdb','tempdb');\r\n\r\nSELECT name\r\nFROM   sys.sysdatabases\r\nWHERE  status & 65536 = 65536 \r\n AND name IN ('master','msdb','tempdb');<\/pre>\n
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<\/code> value. It is 2 to the 16th power:<\/p>\n
SELECT POWER(2,16); --returns 65536<\/pre>\n
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:<\/p>\n
    \n
  • GET_BIT – See if a bit is set in a binary value<\/li>\n
  • SET_BIT – Set a bit in a binary value and return the new value<\/li>\n
  • LEFT_SHIFT, RIGHT_SHIFT – Shift all the bits left or right in a binary value<\/li>\n
  • BIT_COUNT – count the number of bits set in a value<\/li>\n<\/ul>\n
    In our previous query of status and checksum, you could rewrite as:<\/p>\n
    SELECT name, GET_BIT(status,10) AS is_readonly,\r\n\t\t\t GET_BIT(status,16) AS is_checksum\r\nFROM   sys.sysdatabases\r\nWHERE  name IN ('master','msdb','tempdb');<\/pre>\n
    This returns:<\/p>\n
    name\u00a0 \u00a0 \u00a0 \u00a0 \u00a0is_readonly is_checksum
    \n------------ -----------\u00a0 -----------
    \nmaster\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1
    \nmsdb\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1
    \ntempdb\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a00\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1<\/code><\/p>\n
    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.<\/p>\n
    The rest of the bit functions are less useful in any solution I have ever seen, but they are interesting. For example:<\/p>\n
    DECLARE @value VARBINARY(64) = 0;\r\n\r\nSET @value = SET_BIT(@value,10);\r\n\r\nSELECT @value <\/pre>\n
    This returns:<\/p>\n
    -------------<\/code>
    \n0x00000400<\/code><\/p>\n
    Which is binary for 1024<\/code> (cast that value to int<\/code> 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\u2019s set bits 1, 2, 3, and 4, then shift those bits right:<\/p>\n
    SELECT SET_BIT(SET_BIT(SET_BIT(SET_BIT(0,1),2),3),4);<\/pre>\n
    This outputs 30.. which may confuse you (it did me!), there is a bit 0 also. So it is 2 + 4 + 8 + 16 = 30<\/p>\n
    Shifting 30 right 1 position:<\/p>\n
    SELECT RIGHT_SHIFT(30,1);<\/pre>\n
    This outputs 15, which is now 1 + 2 + 4 + 8. Let’s shift it again:<\/p>\n
    SELECT RIGHT_SHIFT(30,2);<\/pre>\n
    This is analogous to RIGHT_SHIFT(RIGHT_SHIFT(30,1),1)<\/code> and returns 7. Bits fall off to the side, so, for example:<\/p>\n
    SELECT LEFT_SHIFT(RIGHT_SHIFT(30,100),100);<\/pre>\n
    Returns 0, because the first set of RIGHT_SHIFT<\/code> statements pushed the bits al the way off of the map. Swap the calls:<\/p>\n
    SELECT RIGHT_SHIFT(LEFT_SHIFT(30,100),100);<\/pre>\n
    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!<\/p>\n
    Finally, let’s look at the BIT_COUNT<\/code> function. Using the 30 value, which we know is 2 + 4 + 8 + 16. Next, execute the following:<\/p>\n
    SELECT BIT_COUNT(30);<\/pre>\n
    And you will see that it returns 4, telling us there are 4 bits that are 1.<\/p>\n
    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!<\/p>\n
     <\/p>\n","protected":false},"excerpt":{"rendered":"
    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.…<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143531],"tags":[],"coauthors":[19684],"class_list":["post-95170","post","type-post","status-publish","format-standard","hentry","category-blogs","category-t-sql-programming-sql-server"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95170","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95170"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95170\/revisions"}],"predecessor-version":[{"id":95176,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95170\/revisions\/95176"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95170"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95170"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95170"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95170"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}