Bitmask (Bitmask in SQL Server and C#)

What is is a bitmask?

Suppose you have a web page where you have a checkbox list (multiple check boxes). The user can select one or many of these check boxes. To keep a track of the checkboxes the user ticked, you can select from two popular options

1> Have a boolean column in the database for each checkbox. (So if you have 10 check boxes, then 10 columns)
or
2>Have just one column that holds a value corresponding to the check boxes ticked.

If you like option 2, then bitmask is your friend.


An example explaining the concept of bitmask

 Suppose I have a webpage where the user has to select the fruits he/she likes. For each fruit he/she selects, I have a binary value assigned to it. (Note that every binary position is assigned to one fruit)

Apple:        00001
Orange:      00010
Grapes:      00100
Mango:      01000


If the user selects say apple and mango, then this is what I would do

1>Add the two binary values
00001+01000=01001

2>Convert to integer
binary 01001= decimal 9

3>Save 9 in the database

Note here that the column that saves a bitmask value in database is defined as an integer.

I know at the first sight, it might seem like some information is lost in the process. But its not. Think of it like this, every position in the binary representation is reserved for one check box. So the binary representation of the stored value will have a 1 in the 10s position only if orange was selected. Try it out yourself.

When you actually implement this in an application, you don't need to convert anything into binary.  What is explained above is the underlying concept. When you implement, you directly use the integer values. Notice that the integer values are double of the previous value.

Binary	Decimal
1	1
10	2
100	4
1000	8
10000	16
100000	32
1000000	64

and so on.

When you use bitmask in SQL server, the bitwise AND (&) is a very useful feature. What bitwise and does is that, it returns what is common between the two value compared.

Suppose I have a value 9 saved in the database. If I want to see if Mango (represented by binary 1000 which is decimal 8) was one of the selected checkboxes, I could use Bitwise and (&) 

IF( ( 9&8 ) = 8 )

  BEGIN

      PRINT 'mango selected!'

  END

ELSE

  BEGIN

      PRINT ' no mango selected.'

  END

The statement 9&8  does this

1>Convert both to binary

9 decimal = 1001 binary

8 decimal = 1000 binary

2> See which position matches between the two. In this case it is 1 in the thousands position

1000

3>Return the decimal of the common value

binary 1000= 8 decimal.

Therefore ( 9&8 ) returns 8.

Bitwaise OR  can be thought of as pick both.
Bitwise AND can be thought of as pick common.

Using Bitmask in C# code

If you need to represent the above bitmask in C#, this is what you would do

    [Flags]

     enum Fruits

    {

        Apple = 1,

        Orange = 2,

        Grapes =4,

        Mango = 8

    }

Notice the [Flags] attribute on the enum. This attribute means that the enumeration can be treated as a bit field (that is, a set of flags)

If you want to select Apple and orange, you would do this

var appleOrange = Fruits.Apple | Fruits.Orange;//Bitwise OR means pick both

Console.WriteLine(appleOrange.ToString());//Apple,Orange

Console.WriteLine((int)appleOrange);      //3

To see if the selection contains orange, do this

if ((appleOrange&Fruits.Orange)!=0)//Bitwise AND means pick common

    {

        Console.WriteLine("Contains orange");

    }