T-SQL Bitwise Operations

  By Michelle Ufford | February 4, 2009 - 7:38 am | , , ,

I’ve seen bit-product columns from time-to-time, mostly in SQL Server 2000 system tables, but it’s never been something I’ve had to work with. And when I’ve needed to, I’ve known how to figure out which options are selected, i.e. a bit product of 9 means options 8 and 1 are selected. If you’ve ever taken a look at the [status] column on the sysdatabases table (SQL 2000), you’ll know what I’m talking about.

What I’ve never known how to do, until recently, was calculate these options programmatically. That’s why, when I noticed the [freq_interval] on the sysschedules table was a bit-product column, I decided to spend a little time figuring it out. Fortunately for me, a couple of my awesome co-workers, Jeff M. and Jason H., have worked with this before and were able to explain it to me. And, it turns out, it’s actually quite easy.

Let me back up a few steps in case you’re not familiar with this topic. If you check out the Books Online entry for the sysschedules table (2005), you’ll notice the following statement:

freq_interval is one or more of the following:
1 = Sunday
2 = Monday
4 = Tuesday
8 = Wednesday
16 = Thursday
32 = Friday
64 = Saturday

When I looked at the actual value in the table, the schedule has a [freq_interval] value of 42, which is the sum of the bit values for the days selected.

If there were more than 7 options, the bit values would continue to double, i.e. 128, 256, etc. And regardless of how many bit values you select, you’re guaranteed one and only one possible answer, as the sum of all previous bit values will never exceed the next bit value:
1 + 2 = 3
1 + 2 + 4 = 7
1 + 2 + 4 + 8 = 15

Knowing this, I’m able to retrieve the values manually: I start with the highest bit value that does not exceed 42, then subtract it; I repeat until I’m left with 0.

So…
42 – 32 = 10
10 – 8 = 2
2 – 2 = 0

That means my job is scheduled to run on Friday’s (32), Wednesday’s (8), and Monday’s (2).

Now how do I do this with T-SQL? SQL Server provides an operator specifically for this task: the bitwise AND operator (&). For now, I’m going to skip the “why” behind this and just get to the practical application. If you’re interested in the “why,” let me know and I’ll write a follow-up post on binary and logical AND and OR operations.

For example, to use the bitwise AND to find out which days are selected…

Select 42 & 1 As 'Sunday'
    , 42 & 2 As 'Monday'
    , 42 & 4 As 'Tuesday'
    , 42 & 8 As 'Wednesday'
    , 42 & 16 As 'Thursday'
    , 42 & 32 As 'Friday'
    , 42 & 64 As 'Saturday';

… will return …

Sunday      Monday      Tuesday     Wednesday   Thursday    Friday      Saturday
----------- ----------- ----------- ----------- ----------- ----------- -----------
0           2           0           8           0           32          0

If the result is not equal to zero, then that day is selected. Easy as key lime pie, right?

Now let’s take it a step further and create our own working example. Let’s say we’re going to track the characteristics of various objects in a single bit-product column (note: this is not necessarily the best way to accomplish this in the real world, but it’s a good illustration). First, set up a table to use in our example. This table will have a column, [attributes], which will hold the sum of our bit values.

Create Table myTable
(
      id            int identity(1,1)
    , item          varchar(10)
    , attributes    int
);
 
Insert Into myTable
Select 'Broccoli', 200 Union All
Select 'Tomato', 193 Union All
Select 'Car', 276 Union All
Select 'Ball', 292;

Next, we’re going to create a table variable that holds characteristics and their values. We’ll then join these two tables together to see which attributes exist for each item.

Declare @statusLookup Table
(
      attribute int
    , value     varchar(10)
);
 
Insert Into @statusLookup
Select 1, 'Red' Union All
Select 4, 'Blue' Union All
Select 8, 'Green' Union All
Select 16, 'Metal' Union All
 
Select 32, 'Plastic' Union All
Select 64, 'Plant' Union All
Select 128, 'Edible' Union All
Select 256, 'Non-Edible';
 
Select a.item, b.value
From myTable a
Cross Join @statusLookup b
Where a.attributes & b.attribute <> 0
Order By a.item
    , b.value

You should get this result:

item       value
---------- ----------
Ball       Blue
Ball       Non-Edible
Ball       Plastic
Broccoli   Edible
Broccoli   Green
Broccoli   Plant
Car        Blue
Car        Metal
Car        Non-Edible
Tomato     Edible
Tomato     Plant
Tomato     Red

Great, now we know broccoli is edible! Let’s apply a little XML to clean up the results…

Select a.item
    , Replace( Replace( Replace(( 
        Select value 
        From @statusLookup AS b 
        Where a.attributes & b.attribute <> 0 
        Order By b.value For XML Raw)
        , '"/><row value="', ', '), '<row value="', ''), '"/>', '') 
        As 'attributes'
From myTable a
Order By a.item;
item       attributes
----------------------------------------
Ball       Blue, Non-Edible, Plastic
Broccoli   Edible, Green, Plant
Car        Blue, Metal, Non-Edible
Tomato     Edible, Plant, Red

Voila! There you have it, how to use the bitwise AND (&) operator to retrieve multiple values from a bit-product column. Pretty neat stuff!

Special thanks to Jeff M. and Jason H. for their assistance. :)

Happy Coding!

Michelle Ufford (aka SQLFool)

Source: http://sqlfool.com/2009/02/bitwise-operations/

0saves
Save
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.
Tagged , , . Bookmark the permalink.

22 Responses to T-SQL Bitwise Operations

  1. Colin Stasiuk says:
    February 4, 2009 at 7:55 am

    Great article and examples… next time just remember “thinking blogs” should only be posted after noon LOL

  2. Margaret says:
    February 4, 2009 at 12:20 pm

    Well, any 5 year old would argue with you about broccoli being edible and balls being blue, but there you go — what does a 5 year old know?

    Great example of using this function and quite cute as well!

    ê¿ê

  3. Denis Gobo says:
    February 5, 2009 at 5:36 pm

    Michelle,nice post, see also my first post on sqlblog.com

    Speed Up Performance And Slash Your Table Size By 90% By Using Bitwise Logic
    http://sqlblog.com/blogs/denis_gobo/archive/2007/05/29/test.aspx

  4. Michelle Ufford says:
    February 6, 2009 at 7:58 am

    Thanks, Denis, that’s great! The next task for me is to evaluate performance implications, and your article is giving me a jump start on that. :)

  5. Pingback: View (and Disable) SQL Agent Jobs with TSQL : SQL Fool

  6. Ranga says:
    April 16, 2009 at 11:01 am

    Michelle,
    Just saw this website today…its great, like your style!

    I am kind of new to XML, couldn’t understand the & operator used in the below query: Can you please shed some light on this..

    WHERE a.attributes & b.attribute 0

    SELECT a.item
    , REPLACE( REPLACE( REPLACE((
    SELECT VALUE
    FROM #statusLookup AS b
    WHERE a.attributes & b.attribute 0
    ORDER BY b.VALUE FOR XML Raw)
    , ‘”/><row value=”‘, ‘, ‘), ”, ”)
    AS ‘attributes’
    FROM myTable a
    ORDER BY a.item;

  7. Michelle Ufford says:
    April 16, 2009 at 11:42 am

    Hi Ranga,

    Thanks for the feedback! I’m glad you like the site. :)

    The & operator actually has nothing to do with XML; in the example above, XML was just used to help format the result set. The & operator is returning the bitwise value if the bitwise product contains it. To expand on the example above…

    CREATE TABLE #myTable
(
      myWeekDay                 varchar(10)
    , bitwiseProduct            tinyint
    , bitwiseValue              tinyint
    , bitwiseOperatorResults    tinyint
);
 
INSERT INTO #myTable
SELECT 'Sunday', 42, 1, 42 & 1 Union All
SELECT 'Monday', 42, 2, 42 & 2 Union All
SELECT 'Tuesday', 42, 4, 42 & 4 Union All
SELECT 'Wednesday', 42, 8, 42 & 8 Union All
SELECT 'Thursday', 42, 16, 42 & 16 Union All
SELECT 'Friday', 42, 32, 42 & 32 Union All
SELECT 'Saturday', 42, 64, 42 & 64;

    Now run these 2 queries to better examine what we’re doing with the WHERE a.attributes & b.attribute <> 0 syntax:

    SELECT * FROM #myTable;
 
SELECT *
FROM #myTable
WHERE bitwiseOperatorResults <> 0;

    The first query shows you the results of passing each of the values into the bitwise product, with the use of the & operator. The second query shows you only those values that are contained in the bitwise product, which means they are “selected.”

    Does that help? If not, let me know and I’ll expand on it further.

  8. Ranga says:
    April 20, 2009 at 11:46 am

    Thanks a lot for the explanation….

  9. Pingback: Another perl Blog » Blog Archive » The world is amazing: Perl: convert decimal to binary using …

  10. Pingback: Index Defrag Script, v3.0 : SQL Fool

  11. Pingback: Microsoft and DiscountASP.NET news

  12. Pingback: SQL Notes « 36 Chambers – The Legendary Journeys: Execution to the max!

  13. Mike says:
    December 23, 2009 at 11:55 am

    This area of programming is becoming a lost art. Storing and retreiving data by using bit masks (as this article describes) is still a common use of bitwise operators, but there are so many other uses, namely data manipulation and calculation, that are becoming forgotton.

  14. Pingback: Get the Server level default connection properties for SQL Server using T-SQL | Sankar Reddy, SQL Server Developer/DBA

  15. Darren says:
    February 12, 2010 at 6:55 am

    Here’s an interesting “bug” that I’ve found:

    DECLARE @bitvar BIT
    SET @bitvar = 6
    PRINT @bitvar & 1
    PRINT @bitvar & 2
    PRINT @bitvar & 4
    PRINT 6 & 1
    PRINT 6 & 2
    PRINT 6 & 4

  16. Dave says:
    February 16, 2010 at 7:31 am

    You can also do the same trick without creating a lookup table variable, like this:

    [code]
    declare @tmp table
    (
    val int,
    color nvarchar(100)
    )

    insert into @tmp (val, color) VALUES (0, 'black')
    insert into @tmp (val, color) VALUES (1, 'red')
    insert into @tmp (val, color) VALUES (2, 'yellow')
    insert into @tmp (val, color) VALUES (4, 'blue')
    insert into @tmp (val, color) VALUES (8, 'white')

    declare @newval int
    set @newval = 6

    select
    *
    from
    @tmp t cross join
    (SELECT (@newval) as val) n
    where
    t.val & n.val 0
    [/code]

  17. compact treadmill says:
    March 4, 2010 at 12:20 am

    Helpful article and examples, Thanks.

  18. Paulo Pedro says:
    August 1, 2010 at 9:25 am

    DECLARE @bitvar BIT
    SET @bitvar = 6
    PRINT @bitvar & 1
    PRINT @bitvar & 2
    PRINT @bitvar & 4
    PRINT CAST(6 AS BIT) & 1
    PRINT CAST(6 AS BIT) & 2
    PRINT CAST(6 AS BIT) & 4

    This is one of many situations where we should cast values first.

    :)

  19. Nabarupam Deb says:
    March 8, 2011 at 5:57 am

    What to do if the data is bigger than the BIGINT Range?

  20. Quotes says:
    April 29, 2011 at 8:31 pm

    Thanks for sharing this post.It is really worth reading.

  21. recep hazer says:
    November 15, 2011 at 1:17 am

    Here’s an interesting “bug” that I’ve found:

    DECLARE @bitvar BIT
    SET @bitvar = 6
    PRINT @bitvar & 1
    PRINT @bitvar & 2
    PRINT @bitvar & 4
    PRINT 6 & 1
    PRINT 6 & 2
    PRINT 6 & 4

    @bitvar is bit how the hell it supposed to get value 6
    try @bitvar as int there is no bug

  22. Pingback: How to create a concatenated string of row values based on flags in SQL Server | PHP Developer Resource

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>