Bitwise Operators in SQL

Bitwise operators are sometimes very useful in SQL, They provides very clean data and saves data size. This may not show performance improvement for small databases. But databases have tables with millions of records show significant performance.

As an example, suppose we have multiple categories. Lets assume that a product (say ProductA) belong to 3 categories Category2, Category3 and Category5. One way of accomplishing this is to make a foreign key table ProductCategory with Foreign Keys of Product and Category in that table.

Lets see how we can do the same thing using bitwise operators. We have to assign the values in the order 2x to every subsequent category:

Category1   =    1
Category2   =    2
Category3   =    4
Category4   =    8
Category5   =    16
Category6   =    32
Category7   =    64
Category8   =    128

So, if ProductA belongs to 3 categories – Category2, Category3 and Category5, we add values for these 3 categories:

Category2 + Category3 + Category5 = 2 + 4 + 16 = 22

To see which categories are contained, AND operator (&) is useful. All non-containing elements gets zero result.

SELECT 1 & 22
UNION ALL
SELECT 2 & 22
UNION ALL
SELECT 4 & 22
UNION ALL
SELECT 8 & 22
UNION ALL
SELECT 16 & 22
UNION ALL
SELECT 32 & 22
UNION ALL
SELECT 64 & 22
UNION ALL
SELECT 128 & 22

Result:
0
2
4
0
16
0
0
0

On the other side if you add NOT (~) operator, you get 0 (zero) for  containing categories and non-zeroes for non-containing. Sometimes they are also useful in querying the relevant data.

SELECT 1 & ~22
UNION ALL
SELECT 2 & ~22
UNION ALL
SELECT 4 & ~22
UNION ALL
SELECT 8 & ~22
UNION ALL
SELECT 16 & ~22
UNION ALL
SELECT 32 & ~22
UNION ALL
SELECT 64 & ~22
UNION ALL
SELECT 128 & ~22

Result:
1
0
0
8
0
32
64
128

Advertisements

One thought on “Bitwise Operators in SQL

  1. I see a lot of interesting articles here, i know writing articles is time consuming, but i know unlimited
    source of content for your page , just search in google
    – rewriter creates an unique article in a minute

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s