# Bitwise operations in TSQL

Take the following code:

``````Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARy)
set @a = @a | cast (100 as BINARy)
select cast (@a as integer)
``````

Now, shouldn't the output of this code be 5 ?

I have an integer, set it to 0, so in binary its all 0's

then I `OR` it with 1 , so it becomes 0..001

then I `OR` it with 100 in binary, so it should become 00..0101 which, in decimal is 5

The output I actually get is 101

EDIT: for more clarity, I'm trying to accomplish with TSQL what the following code does in C++

``````#include<stdio.h>
#include<iostream>
using namespace std;
int main()
{
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;
cout<<endl<<a;
}
``````
-

``````decimal    binary
100     =  1100100
1       =  0000001
or
101     =  1100101
``````
-
But I explicitly casted 100 to binary – Akash Nov 26 '12 at 18:05
it's no matter of cast , it's bitwise operation 0x64 or 0x01 = 0x65 = dezimal 101 – bummi Nov 26 '12 at 18:08
oh.. Got it. Is there a way to specify binary numbers in T-SQL? – Akash Nov 26 '12 at 18:10
anything is stored binary, just a arrangement (perhaps bad translation) what's the meaning of the data, perhaps you are looking for a funtion for displaying data in binary format as varchar? – bummi Nov 26 '12 at 18:15
True, but like in C I can say 0x00000010 to represent 16 in hexadecimal, or 0b00000010 to represent 2 in binary, is there a similar set of notations for TSQL? – Akash Nov 26 '12 at 18:20

Your cast isn't doing what you think it should.

`select cast(cast(100 as binary) as int)` equals 100. In other words, you are casting the decimal value 100 to binary, not taking '100' in base-2 notation.

``````Declare @a integer
set @a = 0
set @a = @a | cast (1 as BINARY)
set @a = @a | cast (5 as BINARY)
select cast (@a as integer)
``````

Incidentally, if this is new development, I very highly recommend against bit shifting in SQL Server. It's slow, difficult to maintain, and violates first normal form. If it's not, I'll guess that you probably know this already...

To convert binary to decimal, you can create a function. There are other ways of going from integer to display base-2 notation, but this feels like it would be quite slow.

-
Ohk.. I'm actually moving to a SQL role from a C background, and atleast there, bitwise operators are usually noticeably faster than integer operators. I thought it would translate here as well – Akash Nov 26 '12 at 18:17
Not at all, especially considering the role that indexes play in queries. If you have a bitwise operation on an attribute, searching for all of the records in which that integer value has the attribute 'flag' set necessitates scanning the entire table and performing a fairly costly operation on each row. Break this out into separate attributes and you can put an index on the appropriate column(s), turning a required scan + function into a seek with no function. Optimizing C and optimizing T-SQL are two extremely different concepts with relatively little overlap. – Kevin Feasel Nov 26 '12 at 18:28