Bitwise Operator Fundamentals

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    For the first time in my SQL career, I have come across a bitwise operator and to say I'm completely baffled is an understatement. I've read several articles and understand the basics, but I can't put it into practice. This is data in my current environment and what I'm trying to do is return a row for "Calendar - Year To Date". First, the data (these data types match my environment):

    set nocount on

    if object_id('tempdb..#PeriodTypes', 'u') is not null

    drop table #PeriodTypes

    go

    if object_id('tempdb..#Reports', 'u') is not null

    drop table #Reports

    go

    create table #PeriodTypes

    (ID int identity(1,1),

    Period varchar(50),

    Value int,

    Active bit)

    insert into #PeriodTypes values ('None', 0, 0)

    insert into #PeriodTypes values ('Monthly - Inception To Date', 1, 1)

    insert into #PeriodTypes values ('Annual', 2, 0)

    insert into #PeriodTypes values ('Semi Annual', 4, 0)

    insert into #PeriodTypes values ('Quarterly', 8, 0)

    insert into #PeriodTypes values ('Monthly', 16, 1)

    insert into #PeriodTypes values ('Weekly', 32, 0)

    insert into #PeriodTypes values ('Daily', 64, 0)

    insert into #PeriodTypes values ('Rolling 12 Months', 128, 1)

    insert into #PeriodTypes values ('Rolling 6 Months', 256, 1)

    insert into #PeriodTypes values ('Rolling 3 Months', 512, 1)

    insert into #PeriodTypes values ('Rolling 24 Months', 1024, 1)

    insert into #PeriodTypes values ('Rolling 36 Months', 2048, 1)

    insert into #PeriodTypes values ('Annual x2', 4096, 0)

    insert into #PeriodTypes values ('Annual x3', 8192, 0)

    insert into #PeriodTypes values ('Quarterly - Inception To Date', 16384, 1)

    insert into #PeriodTypes values ('Annual - Inception To Date', 32768, 1)

    insert into #PeriodTypes values ('Calendar - Year To Date', 65536, 1)

    create table #Reports

    (ReportTypeID int identity(1,1),

    Title varchar(50),

    PeriodType int)

    insert into #Reports values ('Overall Performance (Original)', 256)

    insert into #Reports values ('Overall Performance', 16272)

    insert into #Reports values ('Referral Drivers', 16272)

    insert into #Reports values ('Sales From Referrals', 16272)

    insert into #Reports values ('Z_KRD', 16272)

    insert into #Reports values ('Blank', 16272)

    insert into #Reports values ('Trend: Category Summary', 49153)

    insert into #Reports values ('Category Summary', 16272)

    insert into #Reports values ('Personnel Ratings', 16272)

    insert into #Reports values ('Z_Internal Test', 16272)

    insert into #Reports values ('Question Detail', 16272)

    insert into #Reports values ('Trend: Question Detail', 49153)

    insert into #Reports values ('Trend: Response Rate', 49153)

    insert into #Reports values ('Key Measure Trend', 16272)

    insert into #Reports values ('Ranking', 16272)

    insert into #Reports values ('Comments', 16272)

    insert into #Reports values ('Key Measures National Average', 16272)

    insert into #Reports values ('Virtual Survey', 112)

    The existing code to return the available reports is:

    select distinct t.ID, t.Period, t.Value, r.PeriodType

    from #Reports r

    join #PeriodTypes t on t.Active = 1

    where t.Value & r.PeriodType = t.Value

    Although "Calendar - Year to Date" is active, it is not being returned because it is failing the evaluation of PeriodTypes.Value & Reports.PeriodType. To understand this, I ran the same query above, but left off the where clause and put the bitwise operation in the select:

    select distinct t.ID, t.Period, t.Value, r.PeriodType, t.Value & r.PeriodType as 'Evaluation'

    from #Reports r

    join #PeriodTypes t on t.Active = 1

    Here is just a sample of the results:

    ID Period Value PeriodType Evaluation

    2 Monthly - Inception To Date 1 112 0

    2 Monthly - Inception To Date 1 256 0

    2 Monthly - Inception To Date 1 16272 0

    2 Monthly - Inception To Date 1 49153 1

    6 Monthly 16 112 16

    6 Monthly 16 256 0

    6 Monthly 16 16272 16

    6 Monthly 16 49153 0

    9 Rolling 12 Months 128 112 0

    9 Rolling 12 Months 128 256 0

    9 Rolling 12 Months 128 16272 128

    I am completely confused as to how 16 & 112 = 16. Or 128 & 16272 = 128. Since I can't wrap my mind around that, I have no idea how to return "Calendar - Year to Date". The examples I've found online show more of a case of BitValue & BitValue = 1 which tells me both items are selected. This makes sense. But how two values equal a different bit value? How do I go about figuring out what value needs to be set where in order to get the year to date to appear? I am embarrased to say I am utterly confused.....

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

    Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

    Let's look over a byte for a second:

    128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

    Why backwards? Because that's actually how it is to the system. If we have the value of 37 in there, we get this:

    128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

    0 0 1 0 0 1 0 1

    I assume you're with me so far. Now, the trick is understanding what a bitwise comparison for AND, OR and XOR mean. We'll avoid XOR, you can get it after a few reads after understanding the rest.

    So, we're going to test 37 with 8. The code is what you've typed: 37 & 8 = 8. It basically checks that the 8 bit is turned on. What's it DOING though?

    128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

    0 0 1 0 0 1 0 1 - 37

    0 0 0 0 1 0 0 0 - 8

    ------------------------------------

    0 0 0 0 0 0 0 0

    They don't match. The particular bit(s) we're checking for (in this case, 8) isn't turned on in BOTH values for the check, so everything zeroes out.

    Now, if we OR'd them ( 37 | 8 = 8)... which is somewhat useless in this scenario but to be clear, you'd get this:

    128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

    0 0 1 0 0 1 0 1 - 37

    0 0 0 0 1 0 0 0 - 8

    ------------------------------------

    0 0 1 0 1 1 0 1 - 45

    OR's are usually against what you need to do but they can be helpful when combining extended flags. In this case, by the way, your OR statement would end up false to the conditional check.

    Now, let's say instead of flag 8, we want flag 4: 37 & 4 = 4

    128 | 64 | 32 | 16 | 8 | 4 | 2 | 1

    0 0 1 0 0 1 0 1 - 37

    0 0 0 0 0 1 0 0 - 4

    ------------------------------------

    0 0 0 0 0 1 0 0 - 4

    Hey, a hit! That's a true condition.

    With those basics, let's take a closer look at some code:

    SELECT

    r.Title,

    CASE WHEN r.PeriodType = 0 THEN 'SET' ELSE NULL END AS [NoSetting],

    CASE WHEN r.PeriodType & POWER( 2, 0) = POWER( 2, 0) THEN 'SET' ELSE NULL END AS [Monthly - Inception To Date],

    CASE WHEN r.PeriodType & POWER( 2, 1) = POWER( 2, 1) THEN 'SET' ELSE NULL END AS [Annual],

    CASE WHEN r.PeriodType & POWER( 2, 2) = POWER( 2, 2) THEN 'SET' ELSE NULL END AS [Semi Annual],

    CASE WHEN r.PeriodType & POWER( 2, 3) = POWER( 2, 3) THEN 'SET' ELSE NULL END AS [Quarterly],

    CASE WHEN r.PeriodType & POWER( 2, 4) = POWER( 2, 4) THEN 'SET' ELSE NULL END AS [Monthly],

    CASE WHEN r.PeriodType & POWER( 2, 5) = POWER( 2, 5) THEN 'SET' ELSE NULL END AS [Weekly],

    CASE WHEN r.PeriodType & POWER( 2, 6) = POWER( 2, 6) THEN 'SET' ELSE NULL END AS [Daily],

    CASE WHEN r.PeriodType & POWER( 2, 7) = POWER( 2, 7) THEN 'SET' ELSE NULL END AS [Rolling 12 Months],

    CASE WHEN r.PeriodType & POWER( 2, 8) = POWER( 2, 8) THEN 'SET' ELSE NULL END AS [Rolling 6 Months],

    CASE WHEN r.PeriodType & POWER( 2, 9) = POWER( 2, 9) THEN 'SET' ELSE NULL END AS [Rolling 3 Months],

    CASE WHEN r.PeriodType & POWER( 2,10) = POWER( 2,10) THEN 'SET' ELSE NULL END AS [Rolling 24 Months],

    CASE WHEN r.PeriodType & POWER( 2,11) = POWER( 2,11) THEN 'SET' ELSE NULL END AS [Rolling 36 Months],

    CASE WHEN r.PeriodType & POWER( 2,12) = POWER( 2,12) THEN 'SET' ELSE NULL END AS [Annual x2],

    CASE WHEN r.PeriodType & POWER( 2,13) = POWER( 2,13) THEN 'SET' ELSE NULL END AS [Annual x3],

    CASE WHEN r.PeriodType & POWER( 2,14) = POWER( 2,14) THEN 'SET' ELSE NULL END AS [Quarterly - Inception To Date],

    CASE WHEN r.PeriodType & POWER( 2,15) = POWER( 2,15) THEN 'SET' ELSE NULL END AS [Annual - Inception To Date],

    CASE WHEN r.PeriodType & POWER( 2,16) = POWER( 2,16) THEN 'SET' ELSE NULL END AS [Calendar - Year To Date]

    FROM

    #reports AS r

    As you can see, you need to consider each flag (or flag combination) like they were their own column. Bitwise operations are the fastest compare you can do on large volumes of data, where multiplying them into many to many tables just overloads the collection. Unless you're looking at millions of reports, I wouldn't even go near this method for a collection this small.

    As a reference, I'll use Bitvectors in my fact tables of billion+ rows for common search patterns so that I can preprocess the search pattern and have the bitvector inform of the success without needing to go to the dimensions. It's very tight for storage and I find them very useful. They're also a specialty tool that should be avoided for common coding.

    EDIT:

    Heh, sorry, left off a piece. In a case like this, you'd use it to pull all monthly reports, doing something like the following:

    DECLARE @PeriodType VARCHAR(50)

    SET @PeriodType = 'Monthly'

    -- Pull all Monthly Reports

    SELECT

    r.title

    FROM

    #reports AS r

    JOIN

    #PeriodTypes AS pt

    ON r.PeriodType & pt.value = pt.Value

    WHERE

    pt.Period = @PeriodType


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • SoCal_DBD

    Hall of Fame

    Points: 3051

    Thank you for the very detailed explanation Craig! That is exactly what I need. That pivot query was very, VERY helpful. I was able to see how when I added up the Value in PeriodTypes where the bit was "set", I could come up with the PeriodType value in Reports. So I added 65536 to the "Overall Performance" row in Reports and now I see "Calendar - Year To Date" as one of the options!! I am so grateful for this forum and contributors such as yourself!

  • Evil Kraig F

    SSC Guru

    Points: 100851

    LSAdvantage (3/18/2013)


    Thank you for the very detailed explanation Craig! That is exactly what I need. That pivot query was very, VERY helpful.

    If it makes you feel any better, I typically have scripts saved (somewhere) of my bitvectors to do exactly that. I usually work with bit patterns more than single flags so it can get complex fast. Think 'skorts', which are skirts and shorts (from the 80s?). Either you can combine the flags skirt and shorts and consider it a different value, or not. Depends on how you want your search conditions to work.

    I was able to see how when I added up the Value in PeriodTypes where the bit was "set", I could come up with the PeriodType value in Reports. So I added 65536 to the "Overall Performance" row in Reports and now I see "Calendar - Year To Date" as one of the options!!

    Dead on target. Another reason that flag values tend to be used, just FYI, is because front end checkbox lists just LOVE those things, and can pass you those values. Under most circumstances a human should not be involved in these values, you want an interface to them.

    I am so grateful for this forum and contributors such as yourself!

    Thanks for the complement, and I agree, this forum has pulled my tail out of the fire once or twice as well. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 716562

  • Jeff Moden

    SSC Guru

    Points: 995116

    Evil Kraig F (3/18/2013)


    Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

    Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

    You spent some time on that post! Nicely done. I know bitwise operations are "old" technology but it still works great and there's fresh faces out there that think AND is a question. You should write an article on it, Craig.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442167

    Jeff Moden (3/18/2013)


    Evil Kraig F (3/18/2013)


    Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

    Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

    You spent some time on that post! Nicely done. I know bitwise operations are "old" technology but it still works great and there's fresh faces out there that think AND is a question. You should write an article on it, Craig.

    And?

  • SQLRNNR

    SSC Guru

    Points: 281205

    Jeff Moden (3/18/2013)


    Evil Kraig F (3/18/2013)


    Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

    Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

    You spent some time on that post! Nicely done. I know bitwise operations are "old" technology but it still works great and there's fresh faces out there that think AND is a question. You should write an article on it, Craig.

    Sure, call it old technology. That's why we see it all over the place in SQL Server from job schedules to SSRS.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Evil Kraig F

    SSC Guru

    Points: 100851

    Lynn Pettis (3/18/2013)


    Jeff Moden (3/18/2013)


    Evil Kraig F (3/18/2013)


    Ahhh, bitvector (bitmask, bitwise operators, combined flags, flagfield, etc...), my old friend.

    Alright, let's start with the basics. You said you've read up on it but some of your difficulty shows that some of the basics didn't quite make it through.

    You spent some time on that post! Nicely done. I know bitwise operations are "old" technology but it still works great and there's fresh faces out there that think AND is a question. You should write an article on it, Craig.

    And?

    Two in one on this.

    @jeff: Not as much as you'd think but more than usual. I appreciate the compliment from you in particular, and definately food for thought. A possible result is formulating in my head, but I want to at least show the particulars as well as the articles that Steve linked.

    EDIT: I should probably mention that if LSAdvantage hadn't been willing to show everything, including giving me sample structure to work with from the getgo, I wouldn't have cared. I've come to sincerely appreciate an honest question where the request shows as much effort as they're asking for from us, and don't mind going out of my way when I have time to give it the proper response when they do. /EDIT /SOAPBOX

    @Lynn: Huh? 😛 I don't get it. I blame the beer... and the ladies who assisted me in drinking it.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply