Interview Question - What will be output for the query ?

Today's question deal with somewhat neglected part of the SQL server. I say Neglected because it is very rarely used.

What will be the output for the following query 

DECLARE @TEST TABLE
(
COL1 SMALLINT,
COL2 SMALLINT
)


INSERT INTO @TEST
SELECT 13,76

SELECT 
COL1 & COL2  AS [&]
,COL1 | COL2 as [|]
,COL1 ^ COL2 AS [^]
,~COL1 AS [~]
,~COL2 AS [~2]
,~ COL1 | COL2 & COL1 ^ COL2 AS Precedence
FROM @TEST


Few of you might know about the operators used in query but for those who haven't seen it before or used it, These are BITWISE operators in SQL server. They perform BIT operations on the expressions used on either side of it. 

In order to understand their working and to answer the question one must know how does integer value gets represented in the binary format. 

Following scale is used to represent integer number 

... 128, 64, 32, 16, 8, 4, 2,1

For our example this much scale is more than enough to represent the numbers..

13 = 8 + 4 + 176 = 64 + 8 + 4

Then one must also know how does BITWISE operator behave when supplied with the values..

AND - 

If either of the I/P is 0 (Bit - False) then O/P is 0. If both the I/P are 1 then O/P is 1 (Bit - True).

OR - 

If either of the I/P is 1 (Bit - True) then O/P is 1. If both the I/P are 0 then O/P is 1 (Bit - False).

X-OR - 

The O/P is 1 (True) only if both the I/P are same else the O/P is 0 (False)

NOT -

O/P is negation of the I/P value i.e. 1 (True) becomes 0 (False) and 0 becomes 1. 

After this we are set to draw the output for the query...

SELECT 

COL1 & COL2  AS [&] -- BITWISE AND

,COL1 | COL2 as [|] -- BITWISE OR

,COL1 ^ COL2 AS [^] -- BITWISE X-OR

,~COL1 AS [~] -- BITWISE NOT

,~COL2 AS [~2] -- BITWISE NOT

,~ COL1 | COL2 & COL1 ^ COL2 AS Precedence -- Precedence Of operator

FROM @TEST

so the output of the query is as follows

For the highlighted part one must know the precedence of the operators. In order to learn more about it please read this link.