Why does SELECT 2^3 return 1 in SQL Server? [duplicate]

Question

Possible Duplicate:
What does a caret (^) do in a SQL query?

Why does SELECT 2^3 return 1 in SQL Server ?

The above was an interview question I came across and could not get why it returns 1.

After googling a bit, I found out that it is a bitwise operator. But I still couldn't understand why 1 is an output.

I have basic knowledge of queries, stored procedure and T-SQL. Can anybody please explain to me:

1. How do I get 1 in SELECT 2^3 ?
2. What is the practical use of such operators ?

And if there is a practical use, then what are the best practices while using such operators

Answer 1

Because ^ is XOR operator.

Truth table for XOR

-------
|^|1|0|
-------
|1|0|1|
-------
|0|1|0|
-------

In another words in result we have have one only when two bits are different.

1 ^ 1 = 0
1 ^ 0 = 1
0 ^ 1 = 1
0 ^ 0 = 0

For You case it its

binary - decimal
00010  - 2
00011  - 3
---------- ^
00001  - 1

More about XOR gate

Usage

For example for mask (bitwise operations) handling or cryptography.

Answer 2

a b a^b
-------
0 0  0
0 1  1
1 0  1
1 1  0

2   0b10
3   0b11
--------
2^3 0b01 = 1

The real practical use is to toggle bits when used as flags.