T-SQL SQL 2008

Question

T-SQL SQL 2008

Ron McCullough

SSC Guru

Points: 63877
More actions
June 19, 2010 at 9:21 pm

#223005

Comments posted to this topic are about the item T-SQL SQL 2008
If everything seems to be going well, you have obviously overlooked something.
Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]
June 20, 2010 at 11:42 pm

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 27 total)

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

tommyh SSCertifiable Points: 6302 More actions · Answer 1

Personally i would rather see them not being used. You dont really gain anything by using them. Only makes the code harder to read. Saving a few keypresses vs loosing readability is in my mind a bad trade.

Well i would think that you wont gain any performance on it anyway. SQL still have to do the math. But havent been able to test... no SQL2008 🙁

honza.mf SSCertifiable Points: 5519 More actions · Answer 2

+= or *= are quite easy, but |= or even ^= wanted a little thinking on bitwise arithmetics.

Nice equestion.

See, understand, learn, try, use efficient
© Dr.Plch

vk-kirov SSCertifiable Points: 7686 More actions · Answer 3

stewartc-708166 (6/20/2010)
These operators ... are definately underused.

Maybe because they are not compatible with SQL Server 2005 and below?

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 4

Good question, but the explanation is lacking. I think that the +=, -=, etc operators are generally even better known than the bitwise operators used here (even if they were done in SQL-2005 compatible format such as SET @b-2 = @b-2 & 1).

The explanation that should have been there is:

& - bitwise AND. Convert operators to binary, do a bit-by-bit AND operation (result bit is 1 if both input bits are 1), then convert the result back to the result data type. In the example here, 5 AND 1 becomes:

5 = 0101

1 = 0001

---- &

0001 = 1

| - bitwise OR. Convert operators to binary, do a bit-by-bit OR operation (result bit is 1 if either input bit is 1), then convert the result back to the result data type. In the example here, 5 OR 1 becomes:

5 = 0101

1 = 0001

---- |

0101 = 5

^ - bitwise exclusive OR. Convert operators to binary, do a bit-by-bit exclusive OR operation (result bit is 1 if exactly one bit is 1), then convert the result back to the result data type. In the example here, 5 OR 1 becomes:

5 = 0101

1 = 0001

---- ^

0100 = 4

% - modulo. I assume the readers here to be familiar with this operator.

(EDIT - attempt to edit formatting of leading spaces)

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

paul.knibbs SSCoach Points: 15320 More actions · Answer 5

Picture didn't appear on the e-mail--had to come here before it showed up. That happen to anyone else?

cengland0 SSCertifiable Points: 6102 More actions · Answer 6

I had to guess because I didn't know the ^= operator but I knew all the others. I was lucky that the other three were specific enough that I guessed correctly.

So why doesn't the Explanation discuss the ^= operator? What is that?

Toreador SSChampion Points: 11365 More actions · Answer 7

I agree with Hugo - I knew the modulus answer, but had no idea what the others were. So got it correct as only one option had the right value for @e 🙂

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Toreador (6/21/2010)
I agree with Hugo - I knew the modulus answer, but had no idea what the others were. So got it correct as only one option had the right value for @e 🙂

Same here, I was instantly 100% sure about the % and low and behold, I didn't have to think any further :w00t:.

John D Right there with Babe Points: 787 More actions · Answer 9

John D

Right there with Babe

Points: 787

June 21, 2010 at 7:35 am

#1182725

Yes, that is how it appeared in my e-mail.

forjonathanwilson SSC Enthusiast Points: 139 More actions · Answer 10

Hugo Kornelis (6/21/2010)
Good question, but the explanation is lacking. I think that the +=, -=, etc operators are generally even better known than the bitwise operators used here (even if they were done in SQL-2005 compatible format such as SET @b-2 = @b-2 & 1).
The explanation that should have been there is:
& - bitwise AND. Convert operators to binary, do a bit-by-bit AND operation (result bit is 1 if both input bits are 1), then convert the result back to the result data type. In the example here, 5 AND 1 becomes:
5 = 0101
1 = 0001
---- &
0001 = 1
| - bitwise OR. Convert operators to binary, do a bit-by-bit OR operation (result bit is 1 if either input bit is 1), then convert the result back to the result data type. In the example here, 5 OR 1 becomes:
5 = 0101
1 = 0001
---- |
0101 = 5
^ - bitwise exclusive OR. Convert operators to binary, do a bit-by-bit exclusive OR operation (result bit is 1 if exactly one bit is 1), then convert the result back to the result data type. In the example here, 5 OR 1 becomes:
5 = 0101
1 = 0001
---- ^
0100 = 4
% - modulo. I assume the readers here to be familiar with this operator.
(EDIT - attempt to edit formatting of leading spaces)

thanks! that makes it all so much more clear. the | operator was confusing me since 5|2 and 5|3 both equal 7...

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 11

I have'nt had to think about bitwise operations since the mid '80s so my brain is mushy now. But the modulo gave it away.

Thanks for the question.

Converting oxygen into carbon dioxide, since 1955.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

Thanks Ron. I learned something more from this question.

Thanks Hugo for explaining it like that.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

webrunner SSC-Dedicated Points: 31862 More actions · Answer 13

paul.knibbs (6/21/2010)
Picture didn't appear on the e-mail--had to come here before it showed up. That happen to anyone else?

Yes, that happened to me, too.

- webrunner

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html