I agree - given that bit data types are packed efficiently internally, there would seem to be few instances when this technique would be useful. IMHO the background knowledge for this is more useful for decoding SHIFT + ALT, etc, or color values in application code, rather than in storing data.
Incidentally, there's a subtle potential bug in 'Decoding the mask - Unmask value using integer representation' - the OS's are being decoded against 3,2, and 1. In fact, they should all be decoded against 3. It's only the order in which the CASE statement is evaluated that makes this example work correctly.
Joe, you're missing the point of what I said with respect to the IP address. While I agree with you that the four columns made more sense, it's a 3rd party application, not my design. I am guessing the database design (the second time around) was done by folks who were looking to do some obfuscation in order to sell their reporting piece (which wasn't very good). Since we were able to quickly determine it was a bitmask, we were able to quickly rewrite our queries to handle the change. And this isn't the only network-type application I've seen this practice with. So it is important to be able to understand bitmasks and hopefully recognize them when they are used in a database.
For me this seems to be a very useful article, as I have to deal with a plethora of (mainly binary) attributes. Up to now I use my own non-binary techniques. From what I understand now, I can combine with this hexadecimal bitmasking technique binary plus numerical values together, which enhances my flexibility. Apart from this, I feel certainly attracted by possible perfomance benefits.
Instead of having to deal with so many columns with different importance, I have to deal now with one column, some T-SQL code, plus separate VB code for the decomposing part. As the number (and meaning) of attributes depends on external conditions, and as the number of attributes will change in the future (depending on the progress of my customers requests) I prefer to deal with two parts of code, instead of having to add or modify again a column for a single attribute. Less work plus more control.
Aside from this: does anybody have a VB decomposing example ?
I do not understand what pg53 means with "… the OS's are being decoded against 3,2, and 1. In fact, they should all be decoded against 3", as the example works perfectly, and I could not profitably use pg53's erratum.
Many thanks to Lee, Leendert.
This article explains the bitmasking approach very clearly and thank you to Lee for this excellent article. Looking forward to seeing your next article on this subject.
"This kind of programming is sending us back to the old days of 1950's mag tape files and bit fiddling with assembly language. "
Hi Joe,In prefacing the article, I could have included the fact that sometimes I like to just "geek" with things, just for the sake of "geeking". You know - Quality Geek Time? However, as I point out often to my students some of those practices in your web article "Ten things that I hate about you" - you have to realize that not everything will be relational, nor will it be in an acceptable normal form. Nor will it be what Dr Codd envisioned. Did he envision blobs in the database? Hardly. Did he envision (gasp) XML data and XML data types? No, not at all.
A toolkit is used most effectively when the appropriate tool is best fit to the job. I did warn that this is not something that should be used all the time, nor is it an attempt to overpower or overcomplicate a solution. I know that it is probably aggravating to you but we have debated things before, and it's good for a lively debate. Remember when I posed a dozen questions to you about NULLs at SQLPass in Grapevine last year? Good, thought provoking arguments make the developer community richer. Blasting a technique that it sends us back to another day ... not so much. Fact is, things change. Did anyone ever think that we'd be using IDENTITY attribute all over creation? Go look at Microsoft's "Best Practice" material - they have them everywhere and strongly recommend them. Normally, I teach my students that 1) they're not relational, 2) they don't describe attributes, and 3) attributes aren't dependent on them. Wonder why they are, then, used so much?Most of your pet peeves, I've agreed with you. For this one, I do think that there is a use for it, regardless of whether it's 1950, 1960, or 2006!
Oh, and thanks the copy of your book. It's great!
You could use a number of different data types for this operation - it's personal preference. Lots of folks might be included to use an integer, it doesn't matter. The varbinary or binary simply allows you to look at the mask and map the representation easier...
Not only 2000, but what about 7? Or 6.5? Or 4.2! Yes all of them work equally as well. It made more sense to title as I did rather than, for a SQL Site, calll it "Intro to Bitmasking in FirebirdSQL"
You have a point. I really don't think that the code is all that difficult, though. What you might gain is replacing brute-force, or lengthy, DDL statements for this fictitious 100 column table. If you're faced with a challenge to capture ten columns - not recommended.
Another example where this was used in a most clever way, where I first saw it as such, was in the original Match.com search engine, created back around 1996-7. The mask stored many, many values which allowed the "first cut" of a search stored procedure to eliminate many unwanted candidates for match making. From that result set, the search process then looked to the actual tables where the data was stored for further refinement, processing using WHERE and EXISTS clauses.Again, just an approach to think about. It isn't applicable for everything that is stored in the database!