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++

using namespace std;
int main()
int a;
a = 0;
a = a | 1;
cout<< a;
a = a | 0b100;

2 Answers 2

decimal    binary
100     =  1100100
1       =  0000001
101     =  1100101
  • But I explicitly casted 100 to binary
    – Akash
    Commented Nov 26, 2012 at 18:05
  • it's no matter of cast , it's bitwise operation 0x64 or 0x01 = 0x65 = dezimal 101
    – bummi
    Commented Nov 26, 2012 at 18:08
  • oh.. Got it. Is there a way to specify binary numbers in T-SQL?
    – Akash
    Commented Nov 26, 2012 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
    Commented Nov 26, 2012 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
    Commented Nov 26, 2012 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.

Your expectation is actually this:

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
    Commented Nov 26, 2012 at 18:17
  • 4
    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. Commented Nov 26, 2012 at 18:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.