March 18, 2013 at 4:11 pm
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:
IDPeriodValuePeriodTypeEvaluation
2Monthly - Inception To Date11120
2Monthly - Inception To Date12560
2Monthly - Inception To Date1162720
2Monthly - Inception To Date1491531
6Monthly1611216
6Monthly162560
6Monthly161627216
6Monthly16491530
9Rolling 12 Months1281120
9Rolling 12 Months1282560
9Rolling 12 Months12816272128
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.....
March 18, 2013 at 4:45 pm
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
ONr.PeriodType & pt.value = pt.Value
WHERE
pt.Period = @PeriodType
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
March 18, 2013 at 5:25 pm
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!
March 18, 2013 at 5:49 pm
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. 🙂
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
March 18, 2013 at 6:14 pm
Great explanation from Craig. We have a few articles on the site as well:
http://www.sqlservercentral.com/articles/Performance+Tuning/usingbitmaskoperators/790/
March 18, 2013 at 8:45 pm
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
Change is inevitable... Change for the better is not.
March 18, 2013 at 9:11 pm
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?
March 18, 2013 at 9:51 pm
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
March 19, 2013 at 4:11 am
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.
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply