Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
}
share|improve this question
add comment

2 Answers

up vote 4 down vote accepted
decimal    binary
100     =  1100100
1       =  0000001
                     or
101     =  1100101
share|improve this answer
    
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
show 1 more comment

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.

share|improve this answer
    
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
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. –  Kevin Feasel Nov 26 '12 at 18:28
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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