[mssql] Bit Manipulations

Introduction

When you use a value in your database or application, the value must be stored somewhere in the computer memory using a certain amount of space. A value occupies space that resembles a group of small boxes. In our human understanding, it is not always easy to figure out how a letter such as as B is stored in 7 seven small boxes when we know that B is only one letter.

Bit manipulation or a bit related operation allows you to control how values are stored in bits. This is not an operation you will need to perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators) are present in all or most programming environments, so much that you should be aware of what they do or what they offer.

Bits Operators: The Bitwise NOT Operator ~

One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~

The bitwise NOT is a unary operator that must be placed on the left side of its operand as in

~Value

Here is an example:

PRINT ~158

To perform this operation, the Transact-SQL interpreter considers each bit that is part of the operand and inverts the value of each bit from 1 to 0 or from 0 to 1 depending on the value the bit is holding. This operation can be resumed in the following table:

Bit	~Bit
1	0
0	1

Consider a number with a byte value such as 248. In our study of numeric systems, we define how to convert numbers from one system to another. Based on this, the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is 0xF8). If you apply the bitwise NOT operator on it to reverse the values of its bits, you would get the following result:

Value	1	1	1	1	1	0	0	0
~Value	0	0	0	0	0	1	1	1

Bits Comparison: The Bitwise AND Operator &

The bitwise & is a binary operator that uses the following syntax

Operand1 & Operand2

This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0. This comparison is resumed as follows:

Bit1	Bit2	Bit1 & Bit2
0	0	0
1	0	0
0	1	0
1	1	1

Imagine you have two byte values represented as 187 and 242. Based on our study of numeric systems, the binary value of decimal 187 is 1011 1011 (and its hexadecimal value is 0xBB). The binary value of decimal 242 is 1111 0010 (and its hexadecimal value is 0xF2). Let’s compare these two values bit by bit, using the bitwise AND operator:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 & N2	1	0	1	1	0	0	1	0	178

Most of the times, you will want the interpreter to perform this operation and use the result in your program. This means that you can get the result of this operation and possibly display it to the user. The above operation can be performed by the following program:

PRINT 187 & 242

This would produce 178

Bits Comparison: The Bitwise OR Operator |

You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:

Value1 | Value2

Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0. This operation is resumed as follows:

Bit1	Bit2	Bit1 | Bit2
0	0	0
1	0	1
0	1	1
1	1	1

Once again, let’s consider decimals 187 and 242. Their bitwise OR comparison would render the following result:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 | N2	1	1	1	1	1	0	1	1	251

You can also let the compiler perform the operation and produce a result. Here is an example:

PRINT 187 | 242

This would produce 251

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:

Value1 ^ Value2

The compiler compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. This operation is resumed as follows:

Bit1	Bit2	Bit1 ^ Bit2
0	0	0
1	0	1
0	1	1
1	1	0

We will again consider decimals 187 and 242. Their bitwise-exclusive XOR comparison would render the following result:

	Binary								Decimal
N1	1	0	1	1	1	0	1	1	187
N2	1	1	1	1	0	0	1	0	242
N1 ^ N2	0	1	0	0	1	0	0	1	73

If the interpreter performs this operation, it can produce a result as in the following example:

PRINT 187 ^ 242;

This would produce 73.