Variable assignments

Question

Post reply

Variable assignments

Evgeny Garaev

SSCertifiable

Points: 7428
More actions
July 22, 2018 at 2:13 am

#354678

Comments posted to this topic are about the item Variable assignments

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

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

HappyGeek SSCoach Points: 18729 More actions · Answer 1

HappyGeek

SSCoach

Points: 18729

July 22, 2018 at 2:14 am

#1998509

Good question, thanks Evgeny

...

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 2

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.

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

SADSAC SSCrazy Points: 2400 More actions · Answer 3

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

Stewart "Arturius" Campbell SSC Guru Points: 72622 More actions · Answer 4

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: 4880 More actions · Answer 5

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: 5863 More actions · Answer 6

nice question, cheers

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

Grant Fritchey SSC Guru Points: 398880 More actions · Answer 7

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

lmalatesta SSChasing Mays Points: 637 More actions · Answer 8

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: 1004311 More actions · Answer 9

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-2 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.

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

LongDriver Old Hand Points: 373 More actions · Answer 10

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

DECLARE @a INT = 2147483646,

@b-2 INT = 2147483647;

SELECT@a = @b-2 ^ @a,

@b-2 = @a ^ @b-2,

@a = @a ^ @b-2;

SELECT @a, @b-2;

- Mike

Jeff Moden SSC Guru Points: 1004311 More actions · Answer 11

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-2 INT = 2147483647; SELECT@a = @b-2 ^ @a, @b-2 = @a ^ @b-2, @a = @a ^ @b-2; SELECT @a, @b-2;
- 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.

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

Jonathan AC Roberts SSCoach Points: 18347 More actions · Answer 12

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

David Burrows SSC Guru Points: 65142 More actions · Answer 13

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-2 INT = 2147483647; SELECT@a = @b-2 ^ @a, @b-2 = @a ^ @b-2, @a = @a ^ @b-2; SELECT @a, @b-2;
- 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: 1004311 More actions · Answer 14

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-2 INT = 2147483647; SELECT@a = @b-2 ^ @a, @b-2 = @a ^ @b-2, @a = @a ^ @b-2; SELECT @a, @b-2;
- 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.

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