# Fun with XOR #1

• Thanks for the question, Don.

Reminds me of my days programming in Assembler.

Peter MaloofServing Data

• 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

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

• This code does a "swap" of two variables without requiring a third temporary variable

Ahem. Why not simply use a single SELECT?

`SELECT @a = @B, @B = @a;`

Set-based logic has many advantages over procedural code - embrace them and use them!

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/

• Sure, I get Monday's version right, and then mess this one up by not paying attention to which variables were getting assigned.

Sigh.

• 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?

`SELECT @a = @B, @B = @a;`

This won't swap the variables because @a is assigned the value of @B before the assignment of @a to @B 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, @a` is probably all you need.

Don Simpson

• 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?

`SELECT @a = @B, @B = @a;`

This won't swap the variables because @a is assigned the value of @B before the assignment of @a to @B 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 remain available.

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/

• 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?

`SELECT @a = @B, @B = @a;`

This won't swap the variables because @a is assigned the value of @B before the assignment of @a to @B 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 remain available.

Hmm. Of course I tested it "before making such claims." Here is the code and the output:

`declare @a int = 1, @B int = 100`

``` select @a = @B, @B = @a select @a, @B 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 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, @B = @a from @t t select *, @a, @B from @t a b ----------- ----------- ----------- ----------- 100 1 100 100 ```

Don Simpson

• Thanks for the question.

• 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! 😀

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/

• 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