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



    I'm not sure about Heisenberg.

  • 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



    I'm not sure about Heisenberg.

  • 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



    I'm not sure about Heisenberg.

  • 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 27 (of 27 total)

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