& 1 > 0 -- syntax help

Question

& 1 > 0 -- syntax help

lduvall

SSCarpal Tunnel

Points: 4869
More actions
May 14, 2015 at 7:44 am

#318220

I am sure this is a very rudimentary question, but I have "googled" and searched and cannot figure it out. My coworker used this syntax in his procedure and when I asked about it, all the others in the room already knew what it did, but I didn't and was too embarrassed to ask for clarification. What is this doing?
WHERE course_type & 1 > 0
The Course_Type is an INT. What is the & (ampersand) doing in this scenario? They said it's a bitmap comparison.
Thanks for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Firstly, never be too embarrassed to ask your colleagues. Everyone started from no where and you'll probably find that a couple of them also don't know what the predicate does and pretended they did, hoping that someone else would ask for clarification.

It is indeed a bitwise operator, an AND in this case (| is OR iirc)

Let's take a CourseType of 5 to demonstrate.

5, in binary (and using just a single byte) is 00000101

1, in binary is 00000001

The & does the AND operation on each bit. AND returns 1 if both bits are 1, otherwise it returns 0

so, 00000101 & 00000001:

00000101

00000001

----------

00000001

That's 1, so when compared to 0 it is greater than zero and that predicate returns true

Now let's try a CourseType of 14. 14 in binary is 00001110

00001110 & 00000001:

00001110

00000001

---------

00000000

That's 0, which is not greater than 0 and so the predicate returns false.

What your colleague is essentially doing here is returning rows where the CourseType is an odd number (1,3,5,7,9,11, etc)

Make sense?

btw, if any developer brought me code with bitwise operations in it, he's have to have a really, really, exceptionally good reason for me not to send it back for rewriting. Firstly it's not obvious, which means it's going to be a pain to maintain later on. Second it's not going to be efficient because SQL can't use an index on CourseType to evaluate that filter because the & acts as a function.

Too many times when I see this, it's the developer trying to prove how clever he is.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

This is bitwise AND. It requires INT operands

Look at

declare @i int = 13

Select @i & 1 bit1, @i & 2 bit2, @i & 4 bit3, @i & 8 bit4

lduvall SSCarpal Tunnel Points: 4869 More actions · Answer 3

Thank you so much for that thorough explanation and for your kind and encouraging words. I understand much better now! I'm going to read it a few more times to truly "get" it, but I don't feel so stupid for not knowing. I've been writing T-SQL for a long time but I just never encountered that before!

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 4

Don't feel stupid for not knowing bit wise operators, they're an elegant weapon from a more civilized age.

Actually they're mostly just an archaic tool from when programmers were fighting over single bits. I had to use them for the first time in years recently when I came across a single byte year that was storing information at the half byte level >.< cry......