Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to Bitmasking in SQL Server, Part 2


Introduction to Bitmasking in SQL Server, Part 2

Author
Message
Mitch Miller
Mitch Miller
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 46
Sorry LP -- I just had a nice big explanation with examples, and this damn website took it and tossed it. Now I'm pissed. I can't even go back to get the text I'd typed.


--Mitch
Leo Peysakhovich
Leo Peysakhovich
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 341
Mitch Miller (5/21/2009)
Sorry LP -- I just had a nice big explanation with examples, and this damn website took it and tossed it. Now I'm pissed. I can't even go back to get the text I'd typed.


In our case we are not violating any db design rules and using it for the application framework general usage while all the rules of relations and normalization are enforced.
I had the same issue with web site when my session was expired within 30 min (while I got meeting)



marshall.jones
marshall.jones
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 69
My understanding is that bitwise operations do not utilize indexes which limits their effectiveness.

Run the following code and the bitwise operation requires many more reads than the = operation (yes I do realise that they are not doing the same thing).

create table #t (id int identity, flags int default 0)

set statistics io off

set nocount on

declare @i int = 0

while @i < 1000000 begin

insert into #t (flags) select @i % 4096

set @i += 1

if @i % 100000 = 0 begin
raiserror ('Inserted %d', 16, 1, @i) with nowait
end

end

set statistics io on

-- 2,000 reads
select count(*) from #t where flags = 1024

-- 2,000 reads
select count(*) from #t where flags & 1024 = 1024

-- create an index
create index i_t on #t (flags)

-- 4 reads
select count(*) from #t where flags = 1024

-- 2,000 reads
select count(*) from #t where flags & 1024 = 1024

drop table #t
mike brockington
mike brockington
SSC-Enthusiastic
SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)SSC-Enthusiastic (162 reputation)

Group: General Forum Members
Points: 162 Visits: 245
I would love to see what this is being compared against, in order to describe this as 'elegant'.

In my dictionary, elegance requires something to be as simple as practicable, but
Bitwise comparisons are really inefficient, and extremely hard to debug by eye.

Throw away your pocket calculators; visit www.calcResult.com


GregoryAJackson
GregoryAJackson
SSC-Enthusiastic
SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)SSC-Enthusiastic (198 reputation)

Group: General Forum Members
Points: 198 Visits: 505
I agree.
I'm not a fan of this in the world of RDMS's

It's "cool", etc. but it doesnt seem practical.

imagine writing queries and reports against the data, etc ?

blech.


GAJ

Gregory A Jackson MBA, CSM
Andrew Peterson
Andrew Peterson
SSC-Addicted
SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)SSC-Addicted (446 reputation)

Group: General Forum Members
Points: 446 Visits: 714
Great article. And nice example use of bitmask for searching.

If you approach the bitmask column for the purpose of high frequency searching, really for "reporting" then it does not violate any normalization rules. Report tables are almost always denormalized. I've seen some real world examples where this approach could really add value for end user searches.

And a great twist on tier to tier data transmittal. Consolidating data at the front end tier into a bitmask for rapid transmittal, and then decomposition at the middle or data tier for storage. This has some potential in selected situations.
The twist is that the time to convert to bitmask (both directions) is faster than transmitting a large volume of data - which in today's environment would be an XML file for best efficency. I cannot immediately think of a use, but it is a new tool, that may come in handy when I least expect it.

As with everything in system design and the New Yankee Workshop, more tools are better. Just use the right tool for the right job!

Thanks again!

The more you are prepared, the less you need it.
marshall.jones
marshall.jones
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 69
I tend to use them all the time, when calling from an application bitmasks provide a easy way to filter data without having to add new parameters.

E.g.

create procedure spc_my_proc
@include_filter int = 0
, @exclude_filter int = 0
as begin

select a.a, a.b, a.c
from table a
where a.flags & @include_flags = @include_flags
and a.flags & @exclude_flags = 0

end


That makes retrieving data easy but you have to be aware that it won't work well with large datasets as it doesn't use an index. I find that pretty easy to read too.
philnewell
philnewell
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 62
Grasshopper, have you done any performance checks on things like;

Select count(*) where bitfield1 = 1 and bitfield2 = 0 and bitfield3 = 1 and.... for up to 64 bits?

What is the impact of indexing 64 bit columns as far as size, speed, reads etc.

As per your comments, for a single bit field it takes 2000 reads.

Phil
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54551 Visits: 40391
Mitch Miller (5/21/2009)
Sorry LP -- I just had a nice big explanation with examples, and this damn website took it and tossed it. Now I'm pissed. I can't even go back to get the text I'd typed.


Mitch,

Heh... man, I feel your pain. Timeouts on this site have been a problem since day 1 of the site. I used to get pretty ticked about it, as well. Learned my lesson... I either type it up in a separate word processor or I do a select all/copy before I click any of the buttons to post or preview.

--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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54551 Visits: 40391
First, this series of articles is good and I won't take anything away from the author on it. Well done.

But, I agree with many of the others when it comes to bit masking. I compare it to all the problems associated with storing CSV information in a column. It's generally a violation of good database practices to store more than one piece of information in a given column and will lead to performance problems not unlike those associated with storing CSV data in a table.

Take the humble "test" problem where you want to store answers given by the person taking the test. First, the test is likely a multiple choice test and that renders bit mapped answers virtually useless because bit mapping can only have two states... not the 4 or 5 that may appear on an MC test. If you store only the fact that a question was answered correctly or not, then you've lost a lot of data. It's usually important to store the actual answer choice to try and determine why the people may have gotten a question incorrect. Of course, on a survey, it would also be important to capture the actual answer.

In cases such as tests, surveys, and maybe even control settings (which radio buttons or check boxes are selected), I'd recommend a long skinney table known as a Name/Value Pair which can be indexed for tremendous speeds and also allows for greatly simplified analysis methods using GROUP BY and other tools.

--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

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search