Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Question of the Day for 24 Oct 2006


Question of the Day for 24 Oct 2006

Author
Message
Site Owners
Site Owners
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10091 Visits: 1
Comments posted to this topic are about the Question of the Day for 24 Oct 2006 posted at http://www.sqlservercentral.com/testcenter/qod.asp?QuestionID=900.
Rob Farley
Rob Farley
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 375
This is that old 'in place swap' routine provided by XOR.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
MattieNH
MattieNH
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2953 Visits: 901

I'm sure Microsoft wouldn't have gone to all the trouble of writing this logic if it weren't useful to someone, but I would love to know under what circumstances it would occur to you that you need to analyze and manipulate bits like this.

Thanks,

Mattie





Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671
Dito... but it's a nice change from the old swap code .
Stephen E. Cook
Stephen E. Cook
SSC Eights!
SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)SSC Eights! (863 reputation)

Group: General Forum Members
Points: 863 Visits: 818
Always a fun trick to show off that you know bitwise operations... but it is slower than just using a third variable for the swap. This has been true in C and C++ for years upon years, and I just tested it in SQL Server 2000 and it is true there as well.

Also, it fails miserably if you try to swap a variable with itself (although that is even less likely to come up in a stored procedure).

-- Stephen Cook
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671

Now why would someone want to swap a variable with itself ????

It would seem to me that someone would have forgotten an if statement somewhere .


Rob Farley
Rob Farley
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 375
It's not a Microsoft thing, and you don't need to analyze and manipulate bits or anything like that - it's just one of the lesser-known features of the XOR function. XOR essentially forms a triangle with two things, such that:

If C = A XOR B, then B = A XOR C and A = B XOR C.

Therefore, you can switch two values by just cycling around the circle...

So...

Position 1 Position 2 Result of XOR
A B C (now put this in Position 1)
C B A (now put this in Position 2)
C A B (now put this in Position 1)
B A

So we've done a nice swap. You don't need to try to complicate matters by understanding WHY xor does this.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Alexander Kovacs
Alexander Kovacs
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2547 Visits: 563

The question I had, was not the XOR swap but the following,

When I write “Select @variable1 =… , @variable2 =@variable1 + … ;”

How is it possible that the result @variable1 is used for computation of @variable2

I expected this, but it strike me as odd.

Second question I had was the following

When the “select” command is used for variables. Is @variable2 or @variable1 first calculated.


Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20931 Visits: 9671

This should answer it :

DECLARE @a int, @b int, @c int, @d int
Select @a = 1, @b = @a + 1, @c = @b + 1, @d = @c + @b + @a
Select @a as a, @b as b, @c as c, @d as d

a b c d
----------- ----------- ----------- -----------
1 2 3 6

(1 ligne(s) affectée(s))


Rob Farley
Rob Farley
SSC-Enthusiastic
SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)SSC-Enthusiastic (166 reputation)

Group: General Forum Members
Points: 166 Visits: 375
It's not something I would ever suggest someone really relied upon - but in a scenario, SQL will always go through the select clause one by one.

Of course, the way that it prepares the data it needs to do those calculations (ie, ordering of joins, that type of thing) may change according to the statistics. But you can generally rely upon the select clause.

Hope this helps,

Rob

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://sqlblog.com/blogs/rob_farley
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search