Before we get into bitwise operators, we should be aware of the term “Binary value” .Binary value means, denoting the value in binary format (ones and zeros).

For example

Binary value for 25 is 00011001

(Convert Decimal to Binary online from here , Please go here to learn the binary conversion logic)

Bitwise operator works on binary format data of the value. Point to be noted here is, bitwise operators can be applying only on any of the data types of the integer data type category.

Bitwise operators in SQL

Bitwise operators are introduced from Sql server 2008 version. We have 7 types of bitwise operators in MS SQl.

  1. Bitwise AND (&)
  2. Bitwise AND EQUALS(&=)
  3. Bitwise OR(|)
  4. Bitwise OR EQUALS(|=)
  5. Bitwise Exclusive OR(^)
  6. Bitwise Exclusive OR EQUALS(^=)
  7. Bitwise NOT(~)

We can check how bitwise operator works in detail with AND(&) and Exclusive OR(^)

Bitwise AND (&)

Bitwise And(&) operator performs logical AND on each bit on two values, which means, both bits from two ends should be 1 to get the 1 else result will be 0.

For example, check the following image which contains query and result of '&' operator on 2 values.

Explanation

Binary value for 25, 20 are 00011001 and 00010100 respectively. Now the calculation will be done as follows. If two bits are 1 then the result will be 1 otherwise 0.



Bitwise Exclusive OR(^)

Bitwise Exclusive OR(^)[generally we call it as Caret symbol] operator performs bitwise exclusive or on each bit on two values which means, only one of the two bits should be 1 to get 1 as result else we get 0 .

For example, check the following image which contains query and result of '^' operator on 2 values.

Explanation

Binary value for 25, 20 are 00011001 and 00010100 respectively. Now the calculation will be done as follows.

When we are applying Bitwise Exclusive OR, result will be 1 when only one of the two bits is 1 else 0 even we have two bits are 1.