SQL Integer and '&' sign

Question

Is somebody able to explain what the below query is actually doing?

SELECT (Convert(int, 33558529) & 4096),((Convert(int, 33558529) & 1048576))
FROM dbo.example

Why does the first part return 4096 and the second part returns 0?

Answer 1

The & sign in T-SQL is the bitwise AND. It is used for bitwise comparison on numbers.

Why does the first part return 4096 and the second part returns 0?

Because the big number (33558529) includes the 4096 bit, but does not contain the 1048576 bit.

---

I find it easier to understand when you use smaller numbers, and write it out in binary. Suppose the big number you're checking is actually 9, written as binary 9 is

8 4 2 1
=======
1 0 0 1  <-- 9

If we were to perform bitwise AND logic on the above with the number 8 we would get

8 4 2 1
=======
1 0 0 1    <-- 9
1 0 0 0    <-- 8
-------
1 0 0 0  < -- result of ANDing 9 & 8 = 8

If we did the same exercise but with 2

8 4 2 1
=======
1 0 0 1    <-- 9
0 0 1 0    <-- 2
-------
0 0 0 0    <-- result of ANDing 9 & 2 = 0

Answer 2

& performs a bitwise logical AND operation between two integer values. See the doc.

Here are the integer values converted to binary :

33558529 = 10000000000001000000000001
    4096 =              1000000000000 1 bit match hence 1000000000000 or 4096
 1048576 =      100000000000000000000 0 bit match hence 0