# SQL Bitwise operator can simplify DB design

Welcome to CodeSpread!Frankly speaking, I never tried SQL Bitwise operator but while doing DB design today for one of the projects, I came to know about this operator from one of my friend and I really liked it. To explain it,I will just share my experience and I can assure that you will find it very simple and a useful tool to compact your design.

Scenario:

We have one webpage called “Product Listing” which displays data from table “Product”,which contains data related to ‘n’ number of products. Also, this webpage should have a filter column which follows some rule like-

On click of any filter criteria, products satisfying the filter criteria should be displayed.

Approach 1:

One of our developers gave this approach. Create one FilterGroup Table and FilterGroupItem Table. And Modify Product Table.

Now, Our Product 1 belongs to FiltergroupId 1, which have FilterItems { Item1,Item2 }.

So when user chooses filter 1 and filter 2 , Product 1 will definitely appear.

Approach 2:

Comma separated values or Pipe separated values:

Each Product contains the filters in a comma separated ways. We can write a logic in our code to extract the comma separate values and filter the results.

But both approaches looks ugly. Let’s try approach 3.

Approach 3:

*--latest from codespread--*

Bitwise operator: The bitwise operators are a Transact-SQL extension for use with the datatype integer. These operators convert each integer operand into its binary representation and then evaluate the operands column by column. A value of 1 corresponds to true; a value of 0 corresponds to false.

We have a column BitValue in our FilterItem Tables which corresponds to values like- 1,2,4,8,16,32. Just look at the binary representation of these values

1 | 00001

2 | 00010

8 | 00100

16 | 01000

32 | 10000

These values are obtained by shifting 1 to the right in the binary representation. Now, we have to find out, which of our product belongs to which filter.

To achieve that, we have BitValue column in our Product Table. We will perform ‘AND’ operation between ‘BitValue of Product table’ and ‘BitValue of FilterItem table’.

For Product1, we have BitValue 5. On performing AND operation between the binary representation of 5 [0011] and FilterItem’s Bitvalue values,we find out that Item1 having BitValue [0001] and Item3 having BitVaue [0010] gave result as ‘0’.

That means, ‘product1′ belongs to ‘Item1′ and ‘Item2′ of FilterItem table.

On Click of any of these items, product 1 will definitely get displayed. Same ‘AND’ operation can be performed for other products and their filters can be determined.

We realized few points which can be helpful-

- Save multiple values into one column instead of creating multiple columns.
- Since we will be storing the Bit Mask Values in our database like 1,2,4,8,16,32,64,…,2^n (its left shift Masking example 0001,0010,0100,1000 etc. We are shifting the Bit 1 left side every time for next values).If we take datatype as
**BigInt**for our column then it can have max value up to 9223372036854775807. So approximately up to 50 values (2^50) we can store. - “AND” operator works in C#. net similar to SQL server, So we are free to use it at front end or back end as per our convenience.
- By using this logic, our database queries will be faster since we will be operating on integer data type (Which is faster than Bit data type field and IN operator)
- Also, while fetching the records we are avoiding full table scan.

March 5, 2014

nice superb explaination

March 11, 2014

thanks for the tutorial

I am having a problem.

The feature “Create SQL server database” isn’t available , I can’t select it.

Can you help, please ?