SQLServerCentral / Discuss Content Posted by Lee Everest / Article Discussions / Article Discussions by Author / Introduction to Bitmasking in SQL Server, Part 2 / Latest Posts

RE: Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Tue, 02 Jun 2009 10:29:02 GMT

Here's an implementation of the technique described in part 2 written in an ASP.net web project.[url]http://texastoo.com/post/Implementation-for-Introduction-to-Bitmasking-in-SQL-Server-part-2.aspx[/url]

RE: Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Mon, 25 May 2009 08:41:14 GMT

[quote][b]GregoryAJackson (5/22/2009)[/b][hr]I agree.It's "cool", etc. but it doesnt seem practical.GAJ[/quote]Sweet! Thanks bro[quote][b]GregoryAJackson (5/22/2009)[/b][hr]I agree.I'm not a fan of this in the world of RDMS'sIt's "cool", etc. but it doesnt seem practical.imagine writing queries and reports against the data, etc ?GAJ[/quote]I'm not sure when you would write a report. It's a method - how do you write a report against a method? You mean like a sample of what might get returned? Or maybe a count of how many red-headed 25 year olds you might have in the database? I think that when I was at Match we still had that data available on the customer record for reporting, but when it came time to fire up a search it didn't go to textual customer data. Rather, it pinged a method such as this to fetch matches.Lee

RE: Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Mon, 25 May 2009 08:36:16 GMT

[quote][b]Andrew Peterson (5/22/2009)[/b][hr]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![/quote]Thanks for the kind words, Andrew

RE: Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Mon, 25 May 2009 08:35:20 GMT

[quote][b]mike brockington (5/22/2009)[/b][hr]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, butBitwise comparisons are really inefficient, and extremely hard to debug by eye. [/quote]Hi Mike: I use it loosely here, but let's say comparing to a large string of values, and either parsing a comma separated list or throwing it into an IN and using dynamic SQL. These methods can introduce problems (efficiency, security, etc), so as an alternative I would consider this approach if it fit. Of course, not the solution for every problem.Thanks much

RE: Introduction to Bitmasking in SQL Server, Part 2

Jeff Moden — Sun, 24 May 2009 00:24:46 GMT

[quote][b]marshall.jones (5/23/2009)[/b][hr]That would be the case if I was actually doing what's happening in that example.In practice other constraints on the datasets ensure that the number of reads is in the hundreds if anything.I would much prefer using a single column to store information in my cases than many separate columns. From an academic perspective a column per value is better but in real life I don't want to have to add a new column to a table, add a new index, return a new column in my result sets, add a new parameter into my stored procedures and anything else I've missed. The alternative of adding a new bit value starts to look pretty good in that situation. Horses for courses of course.[/quote]I didn't say anything about a column per value. ;-)

RE: Introduction to Bitmasking in SQL Server, Part 2

marshall.jones — Sat, 23 May 2009 23:11:09 GMT

That would be the case if I was actually doing what's happening in that example.In practice other constraints on the datasets ensure that the number of reads is in the hundreds if anything.I would much prefer using a single column to store information in my cases than many separate columns. From an academic perspective a column per value is better but in real life I don't want to have to add a new column to a table, add a new index, return a new column in my result sets, add a new parameter into my stored procedures and anything else I've missed. The alternative of adding a new bit value starts to look pretty good in that situation. Horses for courses of course.

RE: Introduction to Bitmasking in SQL Server, Part 2

Jeff Moden — Sat, 23 May 2009 13:14:27 GMT

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.

RE: Introduction to Bitmasking in SQL Server, Part 2

Jeff Moden — Sat, 23 May 2009 12:53:57 GMT

[quote][b]Mitch Miller (5/21/2009)[/b][hr]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.[/quote]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.

RE: Introduction to Bitmasking in SQL Server, Part 2

philnewell — Sat, 23 May 2009 10:44:11 GMT

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

RE: Introduction to Bitmasking in SQL Server, Part 2

marshall.jones — Fri, 22 May 2009 22:53:49 GMT

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 = 0as beginselect a.a, a.b, a.cfrom table awhere a.flags & @include_flags = @include_flags and a.flags & @exclude_flags = 0endThat 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.

RE: Introduction to Bitmasking in SQL Server, Part 2

Andrew Peterson-472853 — Fri, 22 May 2009 19:20:03 GMT

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!

RE: Introduction to Bitmasking in SQL Server, Part 2

GregoryAJackson — Fri, 22 May 2009 14:40:48 GMT

I agree.I'm not a fan of this in the world of RDMS'sIt's "cool", etc. but it doesnt seem practical.imagine writing queries and reports against the data, etc ?blech.GAJ

RE: Introduction to Bitmasking in SQL Server, Part 2

mike brockington — Fri, 22 May 2009 09:04:55 GMT

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, butBitwise comparisons are really inefficient, and extremely hard to debug by eye.

RE: Introduction to Bitmasking in SQL Server, Part 2

marshall.jones — Thu, 21 May 2009 19:32:23 GMT

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 offset nocount ondeclare @i int = 0while @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 endset statistics io on-- 2,000 readsselect count(*) from #t where flags = 1024-- 2,000 readsselect count(*) from #t where flags & 1024 = 1024-- create an indexcreate index i_t on #t (flags)-- 4 readsselect count(*) from #t where flags = 1024-- 2,000 readsselect count(*) from #t where flags & 1024 = 1024drop table #t

RE: Introduction to Bitmasking in SQL Server, Part 2

Leo Peysakhovich — Thu, 21 May 2009 16:35:58 GMT

[quote][b]Mitch Miller (5/21/2009)[/b][hr]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.[/quote]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)

RE: Introduction to Bitmasking in SQL Server, Part 2

Mitch Miller — Thu, 21 May 2009 13:01:21 GMT

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.

RE: Introduction to Bitmasking in SQL Server, Part 2

LP-181697 — Thu, 21 May 2009 11:55:21 GMT

[quote][b]Mitch Miller (5/21/2009)[/b][hr][quote][b]LP (5/21/2009)[/b][hr]Let me explain how we using this methodology without violation of basic principles. We using it for metadata lookup tables and each table has a primary key. There is enumeration column in each table one to one to primary key. So, when application is picking up multiple rows it get combined value and translate it on the front end. But in database all relationships are done through the primary keys.[/quote]That doesn't seem to address the "basic principle" of not storing multiple attributes/values in a single column. That, too, is part of normalization.Besides the comments that have been posted already about why bitmasking is not *generally* a good idea, I've not seen anyone mention how database engines can leverage multiple indexes with multiple criteria to quickly find a resulting rowset.Storing multiple values into a single column will ALWAYS require a row scan to find rows that match the criteria. ALWAYS! (Although it was mentioned earlier that one might limit the domain with additional criteria such as Zip Code, which could allow at least a partial indexed search.)Additionally, bitmasking is limited to the size of the object being used to store the actual value. In the examples the author gave, I believe 64 bits is the limit, and that's a hard limit. If you go beyond that, you need more columns and now your code has to know which data is stored where *and* how to access it.In short, I think what I'm really tryin' to say is that bitmasking is okay in some situations, but it's not really a scalable solution (either in values stored, nor data rows), eliminates the server's ability to make statistics and use those to resolve queries, can't be indexed (or at least offers no value of being indexed) and finally, requires loads more documentation than just a single column.-- Mitch[/quote]create table test (code char(2) primary key, descr varchar(100), enumvalue bigint)insert into test (code,descr,enumvalue)values ('c1', 'code 1', 1)insert into test (code,descr,enumvalue)values ('c2', 'code 2', 2)insert into test (code,descr,enumvalue)values ('c3', 'code 3', 4)Now, when you select you get result as one value. If value is 5 then you select code 1 and 4. How it is violate the design if combination is used only in stored procedure and translation on front end. Database perfectly storing 1 row and link one row to another table. I am not discussing why we need it.

RE: Introduction to Bitmasking in SQL Server, Part 2

philnewell — Thu, 21 May 2009 11:14:50 GMT

I am a big proponent of bitmasks but I have found that other SQL developers have a hard time coming to grips with the concept. (I am an engineer by trade, maybe that helps?)When implementing bitmaps (or flags is another term) I suggest providing helper functions or stored procs to i) make the SQL more readable and ii) minimize errors in using bitmaps associated with using the wrong bitwise operator or bitmask.I have written stored procs that will update (set/reset) individual bits based on the flagname (stored in a reference table). I also allow the user to pass the table and the keys/values to the dataset to be updated and dynamically generate the where clause.Regarding performance, I was under the impression that using bitwise operators in a where clause was very efficient, but I may be completely wrong.Is [ColumnName] & FlagNumberMask quicker thanfield1 = 1 and field2 = 1 and field3 = 1 and field4 = 0....Would every bit field have to be indexed???

RE: Introduction to Bitmasking in SQL Server, Part 2

Mitch Miller — Thu, 21 May 2009 10:02:30 GMT

[quote][b]LP (5/21/2009)[/b][hr]Let me explain how we using this methodology without violation of basic principles. We using it for metadata lookup tables and each table has a primary key. There is enumeration column in each table one to one to primary key. So, when application is picking up multiple rows it get combined value and translate it on the front end. But in database all relationships are done through the primary keys.[/quote]That doesn't seem to address the "basic principle" of not storing multiple attributes/values in a single column. That, too, is part of normalization.Besides the comments that have been posted already about why bitmasking is not *generally* a good idea, I've not seen anyone mention how database engines can leverage multiple indexes with multiple criteria to quickly find a resulting rowset.Storing multiple values into a single column will ALWAYS require a row scan to find rows that match the criteria. ALWAYS! (Although it was mentioned earlier that one might limit the domain with additional criteria such as Zip Code, which could allow at least a partial indexed search.)Additionally, bitmasking is limited to the size of the object being used to store the actual value. In the examples the author gave, I believe 64 bits is the limit, and that's a hard limit. If you go beyond that, you need more columns and now your code has to know which data is stored where *and* how to access it.In short, I think what I'm really tryin' to say is that bitmasking is okay in some situations, but it's not really a scalable solution (either in values stored, nor data rows), eliminates the server's ability to make statistics and use those to resolve queries, can't be indexed (or at least offers no value of being indexed) and finally, requires loads more documentation than just a single column.-- Mitch

RE: Introduction to Bitmasking in SQL Server, Part 2

LP-181697 — Thu, 21 May 2009 09:24:45 GMT

[quote][b]Mike Dougherty (5/21/2009)[/b][hr]This still feels to me like it's violating a basic principle of normalization by creating a dependency of multiple (possibly unrelated) points of data on a single field. It's analogous to implementing an object using a memo field to record properties. You have to parse that encoding every time you want to do something with it. Five fields encoded in a single integer can only be indexed one way. Five separate bit fields allows for much greater flexibility in indexing. I admire the geek elegance, but it's not something that 'average Joe' will understand. So for anyone to use this information, we would need a view to turn the values back into something readable. Seems like unnecessary overhead to introduce this abstraction. If I ever inherit a solution like this, I would probably work to replace it with something more obvious. Even if there is a slight performance advantage to bitmask-encoded data, I don't feel it's worth the extra effort for maintenance people to (re)figure it out every time they(we) have to work on it.thanks for letting me add my 2 cents.[/quote]Let me explain how we using this methodology without violation of basic principles. We using it for metadata lookup tables and each table has a primary key. There is enumeration column in each table one to one to primary key. So, when application is picking up multiple rows it get combined value and translate it on the front end. But in database all relationships are done through the primary keys.

RE: Introduction to Bitmasking in SQL Server, Part 2

Trey Staker — Thu, 21 May 2009 08:27:47 GMT

I missed Part 1, can you provide a link to it?EDIT:Never mind I found it at the top of the article:'http://www.sqlservercentral.com/articles/Miscellaneous/2748/

RE: Introduction to Bitmasking in SQL Server, Part 2

Bradley Deem — Thu, 21 May 2009 08:18:28 GMT

Perhaps using Persisted Computed Columns could provide the best of both worlds? Define the attributes as columns accordingly and then use the computed columns to be the bit stuffed column. Then you have the best of both worlds with the denormalization being a computed column. From a record insertion stand point, you would have to put them in their individual columns or provide a stored procedure for splitting the bit mask accordingly.

RE: Introduction to Bitmasking in SQL Server, Part 2

Mike Dougherty — Thu, 21 May 2009 07:58:55 GMT

This still feels to me like it's violating a basic principle of normalization by creating a dependency of multiple (possibly unrelated) points of data on a single field. It's analogous to implementing an object using a memo field to record properties. You have to parse that encoding every time you want to do something with it. Five fields encoded in a single integer can only be indexed one way. Five separate bit fields allows for much greater flexibility in indexing. I admire the geek elegance, but it's not something that 'average Joe' will understand. So for anyone to use this information, we would need a view to turn the values back into something readable. Seems like unnecessary overhead to introduce this abstraction. If I ever inherit a solution like this, I would probably work to replace it with something more obvious. Even if there is a slight performance advantage to bitmask-encoded data, I don't feel it's worth the extra effort for maintenance people to (re)figure it out every time they(we) have to work on it.thanks for letting me add my 2 cents.

RE: Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Thu, 21 May 2009 07:18:26 GMT

Great point, Leo. For over 64 items you'd have to revert back to the method in part one, or make multiple masks to handle the additional options. Thanks much

RE: Introduction to Bitmasking in SQL Server, Part 2

LP-181697 — Thu, 21 May 2009 06:37:31 GMT

I see one problem with this solution. You can store only 64 values (bigint 2^63 ) And even with numeric data type there are limitations.This is where you need to be careful. Leo P.

Introduction to Bitmasking in SQL Server, Part 2

ab5sr — Tue, 28 Apr 2009 02:33:25 GMT

Comments posted to this topic are about the item [B]Introduction to Bitmasking in SQL Server, Part 2[/B]