December 5, 2014 at 6:31 pm
Thanks for the question, Don.
Reminds me of my days programming in Assembler.
December 6, 2014 at 12:51 pm
Fun question - but rather easy for us ancients who programmed with very limited storage half a century ago (as long as we remember that SQL uses ^ for XOR instead if any of its usual math operator meanings (boolean logic and, bitwise and, exponentiation). Of course the title "Fun with XOR #1" rather gave that away, and I reckon 2 points is over the top for this as people should know what XOR does.
I think anyone who had to convert 100 to binary to get the right answer needs to brush up on bitwise ops.
Tom
December 6, 2014 at 9:05 pm
Fun question - but rather easy for us ancients who programmed with very limited storage half a century ago....
+1. I'm wondering if I first saw the XOR-swap technique in K&R (Kernighan and Ritchie's book on C)....
December 7, 2014 at 9:47 am
This code does a "swap" of two variables without requiring a third temporary variable
Ahem. Why not simply use a single SELECT?
Set-based logic has many advantages over procedural code - embrace them and use them!
December 8, 2014 at 7:36 am
Sure, I get Monday's version right, and then mess this one up by not paying attention to which variables were getting assigned.
Sigh.
December 8, 2014 at 9:28 am
Hugo Kornelis (12/7/2014)
This code does a "swap" of two variables without requiring a third temporary variable
Ahem. Why not simply use a single SELECT?
This won't swap the variables because @a is assigned the value of @b-2 before the assignment of @a to @b-2 occurs, resulting in (in this case) both variables equaling 100. If you really wanted to do an easy swap in a function or sproc, then just reversing the return order select @b-2, @a is probably all you need.
Don Simpson
December 8, 2014 at 11:15 am
DonlSimpson (12/8/2014)
Hugo Kornelis (12/7/2014)
This code does a "swap" of two variables without requiring a third temporary variable
Ahem. Why not simply use a single SELECT?
This won't swap the variables because @a is assigned the value of @b-2 before the assignment of @a to @b-2 occurs, resulting in (in this case) both variables equaling 100.
Suggestion: try it before making such claims.
SQL Server is a set-based language, meaning that it has to ensur that the results are "as if" the entire SELECT runs at the same time. In other words, throughout the execution of the SELECT, the "old" values of @a and @b-2 remain available.
December 8, 2014 at 12:30 pm
Hugo Kornelis (12/8/2014)
DonlSimpson (12/8/2014)
Hugo Kornelis (12/7/2014)
This code does a "swap" of two variables without requiring a third temporary variable
Ahem. Why not simply use a single SELECT?
This won't swap the variables because @a is assigned the value of @b-2 before the assignment of @a to @b-2 occurs, resulting in (in this case) both variables equaling 100.
Suggestion: try it before making such claims.
SQL Server is a set-based language, meaning that it has to ensur that the results are "as if" the entire SELECT runs at the same time. In other words, throughout the execution of the SELECT, the "old" values of @a and @b-2 remain available.
Hmm. Of course I tested it "before making such claims." Here is the code and the output:
declare @a int = 1, @b-2 int = 100
go
----------- -----------
100 100
What you suggest DOES work for tables/columns:
declare @t table (a int, b int)
insert @t (a, b) values (1, 100)
update t
set a = t.b, b = t.a
from @t t
select * from @t
a b
----------- -----------
100 1
The two can be combined to demonstrate the difference in behavior:
declare @a int = 1, @b-2 int = 100
declare @t table (a int, b int)
insert @t (a, b) values (1, 100)
update t
set a = t.b, b = t.a , @a = @b-2, @b-2 = @a
from @t t
a b
----------- ----------- ----------- -----------
100 1 100 100
Don Simpson
December 8, 2014 at 1:03 pm
Thanks for the question.
December 8, 2014 at 3:23 pm
DonlSimpson (12/8/2014)
Hmm. Of course I tested it "before making such claims."
Ouch!
I stand corrected. My apologies.
I do believe that this is a violation of the ANSI standard. Maybe not of the letter of the standard (I am not even sure if variables are included in the standard at all), but definitely a violation of the spirit.
I did a brief search of Books Online to see if the T-SQL specific behaviour is explicitly documented, but I did not find any description, either of the observed behaviour, or of the behaviour that I expected. I do not have the time for an in-depth search at this time, unfortunately.
Thanks for correcting me! 😀
December 8, 2014 at 4:07 pm
I do believe that this is a violation of the ANSI standard. Maybe not of the letter of the standard (I am not even sure if variables are included in the standard at all), but definitely a violation of the spirit.
I think you're right. SQL-99 includes local variables, but I don't think multi-variable assignment is described.
Don Simpson
February 6, 2015 at 10:17 am
But "Fun with XOR #1" was not in the title in the e-mail. And if you're like me, you read the question in the e-mail, came up with an answer, clicked on the "Click here" link, and then scanned for the correct answer (assuming that this was a math problem) in the list of answers. You might want to be more careful with keeping the questions consistent between the e-mail and the web site.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply