RSS Source:  http://www.damba.net

Flexible T-SQL:  Bending T-SQL to Get Around Everyday Programming Obstacles

My last blog post has evolved into a presentation on T-SQL that will be given in June at the Macon .NET User’s group.  The presentation will cover three parts: Using OpenXML to pass arrays to stored procedures, using Partion Over and Row_Number to narrow down queries, and using Bitwise Operators to generator more flexible queries.   This blog post continues the Bitwise Operator entry.

First, a quick note about Bitwise – it must be an integer.  That presents a limit of 32 different bitwise categories for SQL Server 2005.  That’s because the integer on SQL Server is 4 bytes, which yields 2^31.

We want to make an easy way to directly connect a Car table with the Bitwise Options table.  As noted in the first part of this post, Bitwise operators require specifying the categories: e.g. to find Option 8, the query must be:

Select CarId from Car where OptionId & 8 = 8. 

That’s fine, except this step must be repeated for each Option:

                Select CarId from Car where OptionId & 8 = 8 or OptionId & 16 = 16.

This itemization will get tedious after awhile, especially if the need is felt to create suboptions to extend the 32 option limitation.  Fortunately, there is a way around this by using a WHILE loop in T-SQL.

--This table stores the relevant bitwiseids

Declare @BitTable as Table

(

      BitCodeId int

)

--This section loops through the bitwiseids and places them in the stored procedure

DECLARE @bitMask INT

DECLARE @counter INT

BEGIN

      SET @bitMask = 1

      SET @counter = 1

      WHILE (@counter < 31)

      BEGIN

            IF ((@OptionId & @bitMask) = @bitMask)

            BEGIN

                  Insert Into @BitTable (BitCodeId) Values (@BitMask)              

            END

            SET @bitMask = @bitMask * 2

            SET @counter = @counter + 1

      END

      -- Hits here when the 31st bit needs to be checked. We do it here

      -- since if we multiply by 2, the number we get is 2147483648, which

      -- results in an overflow

      SET @bitMask = 2147483647

      IF ((@originalNumber & @bitMask) = @bitMask)

      BEGIN

                   Insert Into @BitTable (BitCodeId) Values (@BitMask)              

      END

END

So the While loop finds all the BitWiseIds that comprise the OptionId number and inserts them into a table.  So what we do then is Cross Join the OptionId on our Car table with our bitwise table and we get the options associated with that join.

Let’s refresh our example tables:

“Option” Table

 16          Cold Air Intake

“Car” table

CarId     Description  OptionId

1              Blue Car               5

2              Red Car               26

“Cross Joining Car Table with BitTable from T-SQL snipped, above.

SELECT Distinct c.CarId

, c.Description

, b.BitCodeId

FROM Car c

Cross join @BitTable b

where  b.BitCodeId & c.OptionId = b.bitcodeid

Yields:

CarId     Description         OptionId

1              Blue Car               1

1              Blue Car               4

2              Red Car                2

2              Red Car                8

2              Red Car               16

So now we have a concise and effective way of applying multiple options to a table using a single, flexible bitwise number.

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.

Welcome to the Home Page of Damba, LLC.  I am Kyle Kelly, a software developer and entrepreneur who specializes in Microsoft Technologies.  I create software solutions for clients using .NET, SQL Server, Silverlight, Javascript, and other technologies. 

I am now expirimenting with Blog software.  The current website was created using the BlogEngine.NET code  As I get more familiar with the software, I will add more features.  At the very least I will create entries about software or business projects that are interesting me.