Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Introduction to Bitmasking in SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, December 4, 2006 4:39 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, June 4, 2015 9:27 AM
Points: 216, Visits: 679

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

Post #327630
Posted Friday, December 8, 2006 1:19 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 14, 2015 12:29 PM
Points: 2, 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

Post #329252
Posted Wednesday, December 13, 2006 6:05 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, April 12, 2015 8:00 AM
Points: 439, Visits: 1,272

Thanks for all the effort with this article!

Excellent to read!





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

Post #330083
Posted Wednesday, December 13, 2006 8:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, April 27, 2015 10:25 AM
Points: 91, 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.

Post #330139
Posted Wednesday, December 13, 2006 8:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 13, 2014 2:51 PM
Points: 19, 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.


Post #330141
Posted Wednesday, December 13, 2006 8:18 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 21, 2015 1:26 PM
Points: 137, Visits: 46
chazmer,

I agree. I have been using bit masking in SQL Server 2000 for over 5 years now.
Post #330143
Posted Wednesday, December 13, 2006 8:21 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 14, 2015 9:02 AM
Points: 84, Visits: 239

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.




Post #330145
Posted Wednesday, December 13, 2006 9:08 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 9, 2015 3:38 PM
Points: 1,570, Visits: 705
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).


Post #330184
Posted Wednesday, December 13, 2006 9:23 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Wednesday, July 8, 2015 8:17 AM
Points: 6,628, Visits: 1,885

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #330194
Posted Wednesday, December 13, 2006 9:39 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 9:11 AM
Points: 657, Visits: 214
Bitmasking does in many ways break relational integrity, as you end up storing multiple distinct values in one column, as opposed to one value per column. (It's like Joe says: One fact one way one place one time.) However, it is very efficient at storing quantities of binary data, and coding for one binary(9) column is vastly simpler than coding for 500 bit columns, and that makes it very tempting. I implemented such a solution once, and regretted it within the month as it became very awkward to analyze the state/setting of one value in my relatively small set of 10.

I think that it depends upon the data being represented. If you had a very tightly coupled set of binary values, such that together they could reasonably and practically be considered a single data entity, then it might be acceptable and reasonable to use this tactic. Otherwise, beware the long-term maintenance costs and data obfuscation! (It may well be that, as with with star schemas, the value returned by this kind of design outweighs the costs of breaking conventional relational architecture. It's important to understand and appreciate the trade-offs you're making when you design systems.)

Philip



Post #330206
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse