Wednesday, July 11, 2012

Storing more than one value in a SQL column (Bitwise Operators ).

Welcome to the world of bitwise operations.  SQL Server has some nice built in functions that allow you to accomplish this fairly easily.
Many a DBA have rolled their eyes at the suggestion of using bitwise operations.  They make a DBAs inherent job function of data integrity more difficult (at least several times over).  In order to understand bitwise operations, you have to understand the fundamentals of binary.  Let’s start at the ground level.  Throughout your entire $600 computer that displays a gazillion colors, spits out Chinese algebraic expressions in fractions of milliseconds, and helps you defeat the greatest WOW (which I personally have never played, so I might be a little wrong here) characters ever known – are all made up of 1’s and 0’s, called bits.  The bits have been compared to a light switch (1 for on and 0 for off).  This is called State
01110011 01110100 01100001 01110100 01100101 is the word STATE in binary.
Eight bits bound together are called a byte.  As you can see when the word state is converted to binary, it is made up of five bytes.  One for each letter.  Thus, one group of 8 bits (a byte) is equivalent to one letter.  The order in which the 1’s and 0’s appear determine which letter is represented. 
Humans count by base 10:
101 = 10  = 10
102 = 10 * 10 = 100
103 = 10 * 10 * 10 = 1000
Computers count by base 2:
21 = 2 = 2
22 = 2 * 2  = 4
23 = 2 * 2 * 2 = 8
Thus, a byte’s highest placement is 28 which equals 2 * 2 * 2 * 2 * 2 * 2 * 2 * 2 or 256.
In binary this is represented as 00000001.
What does this tell us?  We can determine placement of within a byte where and which bits render true (or 1).  Let’s look at this a little more closely:
If we take 11000000.  We have already determined that computers count by base 2.  So if we apply base 2 to our byte, 1 1 0 0 0 0 0 0 will look like 2 4 8 16 32 64 128 256.
Since we have our first two bits render true (or 1’s) we can say that the 2 and 4 bits are active.  Now if we take the active bits and add them together, we get 6.  Remember that a byte is made up of 8 bits represented by base 2 ( 2 4 8 16 32 64 128 256).  Now there are no other two bits in the byte that we can add together to get 6.  Thus, if we store and later retrieve the value 6, we know that it is the first two bits that make up the 6.
Now let’s conceptualize this a little more relevant to the .Net Framework.  Instead of a byte ( which we determined is an array of 8 bits) let’s use checkboxes.  We need to give our checkboxes a value that we can determine if it has been selected or not.  As we saw in our examples above base 2 will work extremely well in this situation.  We’ll create 4 checkboxes like so and give them the following values:
Checkbox1 = 2
Checkbox2 = 4
Checkbox3 = 8
Checkbox4 = 16
So say we have these checkboxes on a form and they represent office equipment.  I don’t know checkbox1 is a fax machine… etc… and you are to click which ones you have in your office.
Instead of storing four separate columns in a database with Boolean values you could use a single column with bitwise operators like so:
If checkboxes 1 & 2 are selected, we know the value will be 6
If checkbox 1 is selected, we know the value is 2
If checkbox 2 & 3 are selected, we know the value is 12
When you add up the totals, there is only one possible solution to which checkboxes were selected to make the total.  Thus, your column would hold a value 6, 2, or 12.  Or if all of them were selected it would be 30 … etc …
In SQL we would use the POWER function to determine which of our bitwise operator checkboxes were selected.  Let’s take the example of checkboxes 2 &3 being selected.  Our value would be 12:
In SQL we could write:
Print 2 & 12 --Line 1
Print 4 & 12 --Line 2
Print 8 & 12 --Line 3
Print 16 & 12 --Line 4

Read in English, this is said as: 2 is in 12.  4 is in 12, 8 is in 12, and 16 is in 12.

When we execute the query, we get the following result:


We know that 0 is off or holding no value in binary.  So any value other than 0 and we know that it holds value.  Thus, we can determine that using ordinals the second and third checkboxes were the ones selected.

So in TSQL we can determine this by the following SQL:

Declare @checkboxValue int
SET @checkboxValue = SELECT somecolumn FROM dbo.sometable 

IF ( (2 & @checkboxValue) = 2 ) PRINT 'Checkbox 1 Selected' 
IF ( (4 & @checkboxValue) = 4 ) PRINT 'Checkbox 2 Selected'
IF ( (8 & @checkboxValue) = 8 ) PRINT 'Checkbox 3 Selected'
IF ( (16 & @checkboxValue) = 16 ) PRINT 'Checkbox 4 Selected'

If we actually put a value in for somecolumn and dbo.sometable that points to our original value of 12, we can accomplish this temporarily and for testing purposes with the following line:

SET @checkboxValue = 12

Then, when we run it – we get:


If we decide that the first two checkboxes were selected, and use the value 6 in our column (or for testing purposes change it to:

SET @checkboxValue = 6

When we run it, we can determine that it is:


Are you limited to a byte’s worth of selections?  Absolutely not.  A byte is 8 bits or 256, so what if we had 9?  Our next value for a checkbox would be 512, then 1024 ..etc..

What is an even easier way of determining whether a checkbox has been selected?

Declare        @checkboxValue1        bit    =    0
        ,    @checkboxValue2        bit    =    0
        ,    @checkboxValue3        bit    =    0
        ,    @checkboxValue4        bit    =    0
        ,    @totalCheckboxValue    int

SET @totalCheckboxValue        = 
    +    POWER(2*@checkboxValue2,2) 
    +    POWER(2*@checkboxValue3,3) 
    +    POWER(2*@checkboxValue4,4) 
IF ( (2 & @totalCheckboxValue) = 2 ) PRINT 'Checkbox 1 Selected' 
IF ( (4 & @totalCheckboxValue) = 4 ) PRINT 'Checkbox 2 Selected'
IF ( (8 & @totalCheckboxValue) = 8 ) PRINT 'Checkbox 3 Selected'
IF ( (16 & @totalCheckboxValue) = 16 ) PRINT 'Checkbox 4 Selected'

So what are we doing here?  We declare a bit variable for each checkbox. Then we give the variable a default value so that we don’t necessarily need to pass in a value.  Also, we need to give it a value so that our POWER method will perform the math correctly later in the SP.  Then we create a total variable to hold our value of all the checkboxes added together.  This is the value that we would then store in the database column.  It is also the value we need in order to evaluate which checkbox was checked to complete our SP.  Which is what we had before and we need to leave it to perform the same function.  Now if you change one of the defaults to a 1 or all, or even…… some… it will tell you which checkbox was changed… Using bitwise operators.

Happy .Netting………

No comments:

Post a Comment