March 29, 2011 at 12:10 pm
I see a lot of older code where variable are given value with a SELECT command, vs. SET:
declare @set int
set @set = 5
select @set
declare @select int
select @select = 10
select @select
Any advantage 1 over the other?
Thanks all
March 29, 2011 at 12:18 pm
Set is lower overhead and faster for simple variable assignments and computations. The select statement is of course capable of an extensive array of set based operations.
For all the the most demanding situations there is no difference between select and set from the perspective of setting variables.
The probability of survival is inversely proportional to the angle of arrival.
March 29, 2011 at 12:25 pm
Thanks!
March 29, 2011 at 12:44 pm
SET has the advantage/disadvantage that any attempt to assign multiple values to the same variable will fail and raise an error.
SELECT has the advantage that you can assign values to multiple variables in a single pass.
For example:
declare @Var1 int, @Var2 int;
select @Var1 = Col1, @Var2 = Col2
from MyTable
where X=Y;
To do the same with SET, you have to use two statements:
declare @Var1 int, @Var2 int;
SET @Var1 = (select Col1
from MyTable
where X=Y);
SET @Var2 =(select Col2
from MyTable
where X=Y);
That means more I/O cycles. In most circumstances, however, the data will be pulled once and cached, so that the second variable gets its value from RAM even if the first one has to go to disk, so it's not as much of an advantage as it might seem.
Where the multi-variable select comes in handy is if you have a whole bunch of variables, and you end up needing to refactor/debug the select statement. You only have to change it once, instead of changing it once per set statement.
I tend to use select for this, for consistency. Since I use select when I need to assign multiple values, I also use it when I assign one, to keep it standardized, instead of going back and forth. Some people prefer the error-raising aspect of set over the coding efficiency of select, and thus use set everywhere.
There are differences that matter here. It's best to know them so you can decide which standard you prefer.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 30, 2011 at 8:50 am
Gus' post is very good. I used to be a SELECT guy, but because SELECT does not throw an error when more than 1 value is returned I have moved to using SET because when I am assigning a value to a variable I am assuming there is only 1 value returned so I want the error when there are multiple values.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 30, 2011 at 9:16 am
There are differences that matter here. It's best to know them so you can decide which standard you prefer.
I think that's one of the best piece of advice I've gotten, probably since "Wear clean underware in case you get hit by a truck."
Seriously, as a noob to TSQL, it's these little things that I hope will help make me not such a noob.
ab
April 11, 2011 at 6:59 am
Maybe I'm a bit late here, but I'll mention that I prefer to use SET over SELECT when assigning variable values that are fixed (and not the results of a query): for me, it's just more obvious when debugging or reviewing code I wrote a long time ago that the SET values are constants, not the result of a DB query.
YMMV,
Rich
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply