Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Bitwise Operator Fundamentals Expand / Collapse
Author
Message
Posted Monday, March 18, 2013 4:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 161, Visits: 869
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.....
Post #1432379
Posted Monday, March 18, 2013 4:45 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,164, Visits: 7,229
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1432397
Posted Monday, March 18, 2013 5:25 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 2:20 PM
Points: 161, Visits: 869
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!
Post #1432406
Posted Monday, March 18, 2013 5:49 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,164, Visits: 7,229
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1432411
Posted Monday, March 18, 2013 6:14 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 2:19 PM
Points: 33,094, Visits: 15,201
Great explanation from Craig. We have a few articles on the site as well:

http://www.sqlservercentral.com/articles/Performance+Tuning/usingbitmaskoperators/790/
http://www.sqlservercentral.com/articles/1251/







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1432418
Posted Monday, March 18, 2013 8:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 36,781, Visits: 31,237
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1432442
Posted Monday, March 18, 2013 9:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:01 PM
Points: 23,062, Visits: 31,589
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1432453
Posted Monday, March 18, 2013 9:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:07 PM
Points: 21,288, Visits: 14,980
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1432461
Posted Tuesday, March 19, 2013 4:11 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:09 PM
Points: 6,164, Visits: 7,229
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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1432548
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse