BitWise Operators in SQL SERVER

Bitwise operators are not in common use but can be very usefull in some scenarios. It manipulates binary operation between two expressions. 


Bitwise operators perform bit manipulations between two expressions of any of the data types of the integer data type category.The operands for bitwise operators can be any one of the data types of the integer or binary string data type categories (except for the image data type)


There are three types of BitWise operators

• & (Bitwise AND)  
• ~ (Bitwise NOT) 
• | (Bitwise OR) 
• ^ (Bitwise Exclusive OR)

We will use following table and data to execute our examples


----------------------------------------------------------------------------------------
CREATE TABLE bitwise
( 
a_int int NOT NULL,
b_int int NOT NULL
);
GO
INSERT bitwise VALUES (50, 75);
GO
----------------------------------------------------------------------------------------

& (Bitwise AND)

The & bitwise operator performs a bitwise logical AND between the two expressions, taking each corresponding bit for both expressions. If the left and right expressions have different integer data types (for example, the left expression is smallint and the right expression is int), the argument of the smaller data type is converted to the larger data type.
The following example creates a table using the int data type to store the values and inserts two values into one row.


Performing bitwise & operation will produce 2 result


SELECT a_int & b_int FROM bitwise;


0000 0000 0011 0010 -- 50
0000 0000 0100 1011 -- 75
--------------------
0000 0000 0000 0010 -- 2

~ (Bitwise NOT)

The ~ bitwise operator performs a bitwise logical NOT for the expression, taking each bit in turn. If expression has a value of 0, the bits in the result set are set to 1; otherwise, the bit in the result is cleared to a value of 0. In other words, ones are changed to zeros and zeros are changed to ones.


SELECT a_int ~ b_int FROM bitwise;


0000 0000 0011 0010 -- 50
1111 1111 1100 1101 -- ~50


0000 0000 0100 1011 -- 75
1111 1111 1011 0100 -- ~75

| (Bitwise OR) 

The bitwise | operator performs a bitwise logical OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.


SELECT a_int | b_int FROM bitwise;


0000 0000 0011 0010 -- 50
0000 0000 0100 1011 -- 75
--------------------
0000 0000 0111 1011 -- 123

^ (Bitwise Exclusive OR)

The bitwise | operator performs a bitwise logical OR between the two expressions, taking each corresponding bit for both expressions. The bits in the result are set to 1 if either or both bits (for the current bit being resolved) in the input expressions have a value of 1; if neither bit in the input expressions is 1, the bit in the result is set to 0.


SELECT a_int ^ b_int FROM bitwise;


0000 0000 0011 0010 -- 50
0000 0000 0100 1011 -- 75
--------------------
0000 0000 0111 1001 -- 121

You can use it as multiple check box logic's and status changes according to rules.