RSS Source:  www.damba.net

This blog entry will be on an underutilized, but very effective, technique in database programming called the Bitwise Operator.

The bitwise operation specifies that each entry has a value that increments in power of 2 steps: 1, 2, 4, 8, 16, etc.

Why is this useful?  Well, it is extremely useful in being able to find particular combinations of options without having to hard-code all options into a table.  For example, let's say that you are a car manufacturer and you offer the following options:  cloth, leather, 6-cylinder, 8-cylinder.  One method of designing the database would be to have an Options table that looks like this:

A Lookup table would then be required to see what items were on each car, as follows:

All well and good.  The problem occurs when more options are added, such as wood trim, catback exhaust, and 11-speaker surround sound system and the client wishes a report on how many 6-cylinder cars were sold with leather and a catback exhaust, and then mentions that the next report needs to include the cars color on it as well.  The reports can be done, however they will become time consuming and more complicated as options are added. 

The solution is the Bitwise operator.  Let's change our above table to bitwise and see how it simplifies adding new options and searching for them.

In bitwise operations, the OptionIds are added together to get the relevant option group.  For example, CarId 132 has an OptionId of 5, which translates to a car with Cloth (1) and 6-cylinders (4).  Meanwhile, CarId #135 with an OptionId of 24 has 8-Cylinders (8) and a Cold Air Intake (16).

The T-SQL to make this search looks like the following: 

OptionId & OptionValue = Result

To search for a list of cars that have 8 cylinders and a Cold Air Intake, the SQL query would be:

Select OptionId from Car Where OptionId &  8 = 8 and OptionId & 16 = 16.

This system allows for easily adding on new options and search criteria without having to recode the project.