It has been a long time since we have puzzle on this blog so let us have a quick puzzle this Friday. It is based on Bit Wise Operator and very simple if you know those operators.

We will guess the output of the SQL script.

For example, here is the select statement:

`SELECT 10 & 10`

Upon executing it will return us answer as a 10.

However, when I execute following two queries, both of the queries give an answer as Zero.

`SELECT 10 & 0;`

SELECT 1 & 100;

Here is the question for you – why is it giving answers as **zero? **Please leave your answer in a comment below.

Reference: **Pinal Dave (http://blog.sqlauthority.com)**

### Like this:

Like Loading...

1010 & 0000 = 0

1100100 & 0000001 = 0

Because bit wise & operation is zero.

e.g. 1

1: 1010

100: 1010

—————

& : 1010 (i.e. 10)

e.g. 2

10: 1010

0: 0000

————–

& : 0000 (i.e. 0)

e.g. 3

1: 0000001

100: 1100100

—————

& : 0000000 (i.e. 0)

It’s interesting.

Binary number operation

SELECT 10 & 0

=> 1010 & 0000 in binary => 0000

SELECT 1 & 100

=> 0000001 & 1100100 in binary => 0000000

Another example is “SELECT 11 & 14; ”

=> 1011 & 1110 in binary => 1010 in binary => 10

It returns zero because of the logical & operation. The first and second bit in each position is not 1. For example: SELECT 111 & 111 equals 111 because each bit in each position is 1, also SELECT 10 &100 equals 0 because the first and second bit in each position is not 1.

bin Dec

00000000 = 0

00000001 = 1

00001010 = 10

01100100 = 100

“The bits in the result are set to 1 if and only if both bits (for the current bit being resolved) in the input expressions have a value of 1; otherwise, the bit in the result is set to 0.”

So, we also can try “10 & 100″ and result well be 0.

Its bitwise and operator..

100

001

Anding from left to right as we do while adding when we were kid.. Gives..

000

And operator works as

0 & 0 is 0

0& 1 is 0

1 & 0 is 0

1 & 1 is 1

Nice trick Sir.. I was wondering what would be a real world scenario of using it..

sometime you have enum define and you need to display data in report which is not in .net.

For example

http://forums.asp.net/p/1957681/5592809.aspx?Re+How+Can+You+Access+An+EF+ENUM

SQL Server always stores Numbers in “Right” precedence.

Ex: 1 & 10 is actually takes the values into buffers as 00000001 and 00000010 . so, if we apply a bit AND the result is 00000000

10 & 00 = 00

001 & 100 = 000

Applying ‘&’ operator on each bit, you get zeroes.

100 = 01100100

1 = 00000001

1100100 & 1 = zero in common

its a bit wisae operation and its tration like and operator

10 & 10 both condition are true it will return 1(10)

and for rest it will false ie (0)

In TSQL bitwise AND (&) operator takes integer values and Convert it into Binary Values by self

And then does bitwise comparison on these 2 Expressions.

AS in example given by you

select 10&10

will be translated as

Select 1010&1010

And the result will be

1010 and then this result is again translated to integer values which is 10

Whether in case of

Select 1&100

this will be translated as

Select 1&1100100 will result in 0 and this will be translated to Integer as 0

10 in binary format equals by 1010

When you perform the bitwise with 0 you get 0

Same with 100 which it’s right most number in binary format is 0 so the result of the logical and with any other number would be 0

Guys, you know it not!

In the first example we need to multiply: 10 x 0 = 0

In the second example we just remove heading “1” from both parts: 100=>00, 1 => 0; now multiply 00 x 0 = 0

Much better explanation than all that bitwisemagic :D

select 10 & 10

here we are comparing the same value it means simple

if(T&T)//it will return true value

{

select T;

}

else

{

select F;

}

select 0 & 1

here we are comparing the same value it means simple

if(F&T)//it will return false value

{

select T;

}

else

{

select F;

}

select 100 & 0

here we are comparing the same value it means simple

if(F&T) //it will return false value

{

select T;

}

else

{

select F;

}

The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0000 1010. Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.

(A & B)

0000 0000 0000 0001

0000 0000 0000 1010

——————-

0000 0000 0000 0000 = 0

The binary representation of 1 is 0000 0000 0000 0001. The binary representation of 10 is 0000 0000 0110 0100 . Performing the bitwise AND operation on these two values produces the binary result 0000 0000 0000 0000, which is decimal 11.

(A & B)

0000 0000 0000 0001

0000 0000 0110 0100

——————-

0000 0000 0000 0000 = 0

The & operator does a bitwise AND on both the integer values by converting them to their binary form.

For eg SELECT 1 & 100;

00000001 —- binary value of 1

01100100 —- binary value of 100

—————-

00000000 — (After applying the AND operator) decimal value of 0

For select 10 & 10

00001010 — binary value of 10

00001010 — binary value of 10

————–

00001010 — (After applying the AND operator) decimal value of 10

Small correction – I meant the output is binary value of 0 & 10 respectively

binary value for 1 and 0 is always 0000 which makes the result 0 (with AND operation)

Please let me know whether this is right?

Pingback: SQL Puzzle – A Quick Fun with Bit Wise Operator – Part 2 | Journey to SQL Authority with Pinal Dave

select 10 & 0

select 10 & 100

10= 00001010

0= 00000000

——————

00000000

Sum of this =0

10= 00001010

100=01100100

——————

00000000

Sum of this =0