SQL Bitwise operator can simplify DB design

database
Database @Image courtesy of ddpavumba/ FreeDigitalPhotos.net

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.

image

Approach 1:

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

image      image    image

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:

image       image

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:

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.

image      image

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.

Author: hershey

A passion for knowledge drives me to do programming, A passion for programming drives me to create something different, A passion for creation drives me to spread the knowledge.

Share This Post On

2 Comments

  1. nice superb explaination

    Post a Reply
  2. 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 ?

    Post a Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

More from CodeSpread:

  • Agile Encounters UnpredictabilityAgile Encounters UnpredictabilityWhat is Agile? Wikipedia says “Agile software development is a group of software development methods based on iterative and incremental development, where requirements and solutions evolve through...
  • SQL fry: PIVOT and UNPIVOTSQL fry: PIVOT and UNPIVOTWhat is PIVOT? In our day-to-day SQL server coding, PIVOT and UNPIVOT are not very familiar words but still holds good if we understand them correctly and know when to use it.Web Definition of PIV...
  • SQL : Clustered and NonClustered IndexSQL : Clustered and NonClustered IndexWe read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL. We have clustered and non-clustered index in SQL, let’s see...
  • IDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property and IDENTITY as SQL FunctionIDENTITY as SQL Property: Almost all the time, We have a table with a primary key and would like to insert a new value automatically in the primary key field. To achieve this, We have IDENTITY ...
  • SQL: Story of IndexesSQL: Story of IndexesI know many people know everything about this topic but as this is one of my favorite topics, I will make sure that I will give you some additional information or knowledge. My first thought abo...
  • SQL: Common Table Expression called CTESQL: Common Table Expression called CTEI remember somebody talked about CTE or Common Table Expression with me and tried helplessly to make me understand it. Honestly, I listened very peacefully but was not able to figure out the exact ...
  • SQL : Transactions and ACID properties.SQL : Transactions and ACID properties.There are always two point of views of transactions. First, the user point of view, for example, customer went to an ATM to retrieve some amount. For him, the transaction might look like, Fo...
  • Verify existence of SQL tables using Object_ID()Verify existence of SQL tables using Object_ID()Why Object_ID() ? Let’s create a SQL table “Employee” [code] CREATE TABLE Employee ( [EmployeeID] [int] IDENTITY(1,1) PRIMARY KEY, [EmployeeName] [varchar](50) NULL, [Salary] [int] NUL...