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: Wednesday, November 19, 2014 9:49 AM
Points: 215, Visits: 652

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, July 8, 2008 9:32 AM
Points: 2, Visits: 3

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: Friday, October 24, 2014 6:42 AM
Points: 438, Visits: 1,260

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 7:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 1,945, Visits: 3,122
SQL is a high-level language designed for maintaining and retrieving data. It is not a low-level, next to the hardware tool. Each column is supposed to be a scalar value, not a structure that decomposes into meaningful parts.

This kind of programming is sending us back to the old days of 1950's mag tape files and bit fiddling with assembly language.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #330113
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: Thursday, May 15, 2014 6:04 AM
Points: 91, Visits: 108

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: Monday, May 19, 2014 11:25 AM
Points: 137, Visits: 37
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: Thursday, September 4, 2014 9:13 AM
Points: 81, Visits: 235

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: Yesterday @ 6:47 AM
Points: 1,570, Visits: 684
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: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

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
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse