SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Introduction to Bitmasking in SQL Server 2005


Introduction to Bitmasking in SQL Server 2005

Author
Message
ab5sr
ab5sr
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1968 Visits: 725

Thanks to Don Bishop, my mentor, for introducing me to this concept almost 7 years ago now! Wow, time flys.

Code snippet for Intro 1 is here:

www.texastoo.com/IntroToBitmaskingPartI.htm



Lee Everest


Don Bishop
Don Bishop
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 4

Great article. I've used this type of bitmasking at match.com to the code the user profiles that are used in very fast matching to potential love mates. It is still working today after 5 years and uses very littly space and computer time in search schemes to return up to 500 possibilities in less than a few seconds from a growing profile database.

Can't wait for the follow-on segments.

Don Bishop, SR DBA Consultant


T_VR
T_VR
SSC Eights!
SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)SSC Eights! (939 reputation)

Group: General Forum Members
Points: 939 Visits: 1272

Thanks for all the effort with this article!

Excellent to read!





What's this "backup strategy" everyone is on about?


Mike Arney
Mike Arney
Old Hand
Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)Old Hand (349 reputation)

Group: General Forum Members
Points: 349 Visits: 109

Seems there should be some mention of the SQL Server bit data type, which internally is implemented as a bitmask. I guess it's a matter of opinion whether a single int column with a bit mask is "cleaner and neater" than 32 individual bit columns, but they both take (just about) the same amount of space in the row.

To me the big reason for using a bitmask was to save space. With the bit datatype, that reason is gone, and I prefer the clarity of individual columns.


chazmer
chazmer
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 32
Good article, but just one suggestion. The title is "Introduction to Bitmasking in SQL Server 2005" but the same code you have given will work just fine in SQL Server 2000 as well.



Ed F
Ed F
SSC-Enthusiastic
SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)SSC-Enthusiastic (149 reputation)

Group: General Forum Members
Points: 149 Visits: 94
chazmer,

I agree. I have been using bit masking in SQL Server 2000 for over 5 years now.
agatti
agatti
Old Hand
Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)Old Hand (316 reputation)

Group: General Forum Members
Points: 316 Visits: 241

Thank you for a thought provoking article. I work with a sales force automation application that puts this concept to excellent use in the formation of primary key values. To facilitate synchronization between the main database and the sale people's laptop systems, each mobile system is assigned a system id. The primary key of each record can then consist of the mobile system which created the record, plus the unique number of the record per the mobile system. In this way, each primary key value in the main system is unique because mobile systems cannot create a key which would duplicate another mobile's primary key. For example, a record in the companies table might be 0x002003000000000A which consists of 8195 and 10. This represents record 10 in the companies table of mobile system 8195. I think this is a superior solution to using a concatenated primary key of a system field and a record number field.





Simon Facer
Simon Facer
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3529 Visits: 724
Well written article, for a pointless technique. Sorry Lee, but I have to agree with Joe (except I'd date it in the 1980's, rather than 1950's). With HDD and memory as cheap as they are, and multi-processor machines / multi-core-processors becoming the norm, what do you really gain from bit-masking? Besides job security (because no-one knows what the heck you're doing in the code).



K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (52K reputation)

Group: Moderators
Points: 52056 Visits: 1917

To some this may be a pointless technique... but understanding how it works can mean the difference between understanding how a vendor's product is storing information and how it isn't. Case in point: a product we used to track web surfing suddenly switched from the a.b.c.d format for IP addresses (they were stored as strings... not the best way, but it was how it was done). We upgraded the product and suddenly the queries and reports started failing. Why? Because the vendor had converted the IP field to a type of integer format. Once you realized they were using bitmaps to store the IPs, it was easy to figure out the IPs and actually made reporting easier.

Also, using bitmaps can save on performance on the network side... think about all the field names, individual values, etc., that are shuttled from app server to database server. Generally it won't make a bit of difference because bandwidth is sufficient and resources are more than adequate, but there are rare cases where it can.



K. Brian Kelley
@‌kbriankelley
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