Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Question of the Day for 24 Oct 2006 Expand / Collapse
Author
Message
Posted Friday, October 20, 2006 12:05 PM
SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, May 18, 2007 3:36 PM
Points: 10,039, 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.
Post #317063
Posted Tuesday, October 24, 2006 1:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
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
Post #317484
Posted Tuesday, October 24, 2006 11:21 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 9:18 AM
Points: 2,620, Visits: 788

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




Post #317663
Posted Tuesday, October 24, 2006 11:41 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617
Dito...  but it's a nice change from the old swap code .
Post #317670
Posted Tuesday, October 24, 2006 11:59 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, September 1, 2014 10:50 PM
Points: 860, Visits: 809
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
Post #317675
Posted Tuesday, October 24, 2006 12:08 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617

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 .

Post #317677
Posted Tuesday, October 24, 2006 5:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
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
Post #317765
Posted Wednesday, October 25, 2006 1:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, September 5, 2014 3:13 AM
Points: 2,547, Visits: 493

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.

 

Post #317819
Posted Wednesday, October 25, 2006 6:28 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Friday, September 12, 2014 10:44 AM
Points: 21,405, Visits: 9,617

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

Post #317883
Posted Wednesday, October 25, 2006 5:18 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, August 2, 2014 1:58 AM
Points: 164, Visits: 368
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
Post #318147
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse