Variable assignments

  • Evgeny Garaev

    SSCertifiable

    Points: 6742

    Comments posted to this topic are about the item Variable assignments

  • HappyGeek

    SSCoach

    Points: 18684

    Good question, thanks Evgeny

    ...

  • Jeff Moden

    SSC Guru

    Points: 997113

    Clever!  Hopefully both values aren't more than 50% of the max for the given datatype.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • SADSAC

    SSCrazy

    Points: 2078

    Brilliant..
    Nice one to start the week on, thanks Evgeny

  • Stewart "Arturius" Campbell

    SSC Guru

    Points: 71907

    Great question.
    Nice trick.

    ____________________________________________
    Space, the final frontier? not any more...
    All limits henceforth are self-imposed.
    β€œlibera tute vulgaris ex”

  • kaspencer

    SSCarpal Tunnel

    Points: 4431

    I got it right, but I was a bit confused by the word "swaping"! I thought: "Is that anything to do with 'vaping'?". Then I realised the question was referring to "swapping".

    You never know: reading my book: "All about your computer" might just tell you something you never knew!
    lulu.com/kaspencer

  • Shayn Thomas

    SSCertifiable

    Points: 5637

    nice question, cheers

    ---------------------------------------------------------------------------------------
    The more you know, the more you know that you dont know

  • Grant Fritchey

    SSC Guru

    Points: 396692

    I actually had to think about that one for a minute. Nice.

    ----------------------------------------------------
    The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
    Theodore Roosevelt

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • lmalatesta

    SSChasing Mays

    Points: 637

    Keep in mind that this trick relies on behavior that is not only undocumented but officially undefined. There is no guarantee that this trick will work with the next SQL Server update.

    Selecting multiple values into variables is also not ANSI standard but I'm less concerned about that.

  • Jeff Moden

    SSC Guru

    Points: 997113

    lmalatesta - Monday, July 23, 2018 10:20 AM

    Keep in mind that this trick relies on behavior that is not only undocumented but officially undefined. There is no guarantee that this trick will work with the next SQL Server update.

    Selecting multiple values into variables is also not ANSI standard but I'm less concerned about that.

    Heh... While what you say is true, I'll take the bet that it'll work with the next SQL Server update... and the next several dozen after that. πŸ˜‰

    The major concern that I have with this trick is that if @a and @B are each more than 50% (or some other combination) of the maximum for the given datatype, then @a+@B will cause a failure.  It would be much safer (and perhaps quicker because there's no math involved) to use the 3 variable method for doing such a swap.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • LongDriver

    Old Hand

    Points: 361

    To prevent an overflow you can do an exclusive OR instead (apologies for the formatting):

    DECLARE @a INT = 2147483646,

    @B INT = 2147483647;

    SELECT @a = @B ^ @a,

    @B = @a ^ @B,

    @a = @a ^ @B;

    SELECT @a, @B;

     

    - Mike

  • Jeff Moden

    SSC Guru

    Points: 997113

    LongDriver - Monday, July 23, 2018 5:58 PM

    To prevent an overflow you can do an exclusive OR instead (apologies for the formatting):


    DECLARE @a INT = 2147483646,
    @B INT = 2147483647;
    SELECT @a = @B ^ @a,
    @B = @a ^ @B,
    @a = @a ^ @B;
    SELECT @a, @B;

    - Mike

    Lordy, I love this community!.  Thanks Mike.  Makes perfect sense.  A group of Exclusive Or gates in hardware is sometimes referred to as an "adder" but with a "wrap around".  Works the same way in software if the software is written correctly.  Nicely done.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jonathan AC Roberts

    SSCoach

    Points: 17334

    This is an example of the XOR swap algorithm:
    https://en.wikipedia.org/wiki/XOR_swap_algorithm

  • David Burrows

    SSC Guru

    Points: 64814

    Jeff Moden - Tuesday, July 24, 2018 12:01 AM

    LongDriver - Monday, July 23, 2018 5:58 PM

    To prevent an overflow you can do an exclusive OR instead (apologies for the formatting):


    DECLARE @a INT = 2147483646,
    @B INT = 2147483647;
    SELECT @a = @B ^ @a,
    @B = @a ^ @B,
    @a = @a ^ @B;
    SELECT @a, @B;

    - Mike

    Lordy, I love this community!.  Thanks Mike.  Makes perfect sense.  A group of Exclusive Or gates in hardware is sometimes referred to as an "adder" but with a "wrap around".  Works the same way in software if the software is written correctly.  Nicely done.

    Meh! Write a CLR to do a simple switch just for audacity πŸ˜› :laugh:

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Jeff Moden

    SSC Guru

    Points: 997113

    David Burrows - Tuesday, July 24, 2018 6:25 AM

    Jeff Moden - Tuesday, July 24, 2018 12:01 AM

    LongDriver - Monday, July 23, 2018 5:58 PM

    To prevent an overflow you can do an exclusive OR instead (apologies for the formatting):


    DECLARE @a INT = 2147483646,
    @B INT = 2147483647;
    SELECT @a = @B ^ @a,
    @B = @a ^ @B,
    @a = @a ^ @B;
    SELECT @a, @B;

    - Mike

    Lordy, I love this community!.  Thanks Mike.  Makes perfect sense.  A group of Exclusive Or gates in hardware is sometimes referred to as an "adder" but with a "wrap around".  Works the same way in software if the software is written correctly.  Nicely done.

    Meh! Write a CLR to do a simple switch just for audacity πŸ˜› :laugh:

    Heh... too funny.  That reminds me that I wrote an enterprise wide disk checker that uses T-SQL to build a bit of dynamic PowerShell that calls WMI that gets executed through xp_CmdShell.and the results are parsed by persisted computed columns on the table that I direct the xp_CmdShell output to and then HTML format the output using T-SQL and send it using CDOSys (instead of sp_send_dbmail) just to piss off a couple of pro-PowerShell/anti-xp_CmdShell/"just because you can" zealots.  It works fine but the PowerShell slows it down a fair bit and this reminded me that I need to fix it to just use xp_CmdShell and WMI.  It'll greatly simplify the code and improve the performance, as well πŸ˜€

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 19 total)

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