May 1, 2009 at 2:07 pm
I looked around here a bit to see if anyone had posted anything about what is better: SET or SELECT when assigning local variable values. I personally have always preferred using SET, as it helps my code stay "cleaner", but making it easier to identify such variable assignments. After catching up on a few QotD's, and seeing most all use SELECT, I had to do something.
What I found searching on the topic as an interesting article posted a few years back...
It states that as of SQL Server 7, bol says that SET is the standard, yet in the complete article, you can see that there is a performance difference when using SELECT for assigning multiple values at once, only a marginal difference for single value assignments. So, what's your perspective and why?
May 1, 2009 at 2:50 pm
guess that I should have posted this as a poll. 🙁
May 1, 2009 at 2:50 pm
I use Select, because I often assign multiple at the same time, and I don't like having one practice for that and another for something else. Just makes it easier on me.
- 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
May 1, 2009 at 2:57 pm
I usually use SET only for one-offs and only because its shorter. As soon as I am into multiple variables or anything else I use SELECT because its faster, shorter and I never get an error because I forgot that SET could not do something.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 2, 2009 at 4:54 am
May 2, 2009 at 1:03 pm
Heh... I'm definitely NOT an ANSI purest and I definitely DON'T believe in the myth of portable code especially when it comes to high performance batch code.
For all the advantage reasons mentioned in the article in the URL cited in the original post, I prefer to use SELECT for variable assignment. Old habits are hard to break, though. Lots of the older programming languages used either LET or SET for variable assignment and, in the absence of coffee, will sometimes revert to using SET. I am trying to break that habit just for readability and some minor performance advantages just like I'm trying to break the habit of using a separate DECLARE for each variable.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2009 at 7:43 am
Thanks for the feedback folks. I should try breaking the old habit too. 😀
May 4, 2009 at 12:13 pm
I think people get hung up on the performance issue because of that article. How often are you assigning a value to a variable? If you are doing some wacky and need to assign a value 1,000s or millions of times, then SELECT is probably best (I've never seen this in the real world myself). But, for the most part you are going some assignment and running a query against the database which is MUCH more of a performance issue than assigning a variable.
I say as long as you are consistent, go with it. I try to be as ANSI compliant as possible, but that's me.
May 4, 2009 at 1:24 pm
I have to vote for It Depends. I tend to do it both ways, it all depends what I am doing. For single variable sets, I tend to use SET. If setting multiple variables based on a query, then I use SELECT.
May 4, 2009 at 1:44 pm
I generally use SET myself - mostly because it prevents issues where you can get the wrong value returned from a SELECT. When using SELECT - if you are selecting from a table it can return incorrect results if you are not careful.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 4, 2009 at 5:42 pm
I'm of the SET for one, SELECT for multiple set.
May 5, 2009 at 6:59 am
Jeffrey Williams (5/4/2009)
I generally use SET myself - mostly because it prevents issues where you can get the wrong value returned from a SELECT. When using SELECT - if you are selecting from a table it can return incorrect results if you are not careful.
I also use SET the majority of the time for this reason. Although I will use SELECT when I know my SELECT will only return 1 value because I am specifying a WHERE clause against a PRIMARY KEY or UNIQUE INDEX/CONSTRAINT.
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
May 5, 2009 at 7:44 am
Jack Corbett (5/5/2009)
Jeffrey Williams (5/4/2009)
I generally use SET myself - mostly because it prevents issues where you can get the wrong value returned from a SELECT. When using SELECT - if you are selecting from a table it can return incorrect results if you are not careful.I also use SET the majority of the time for this reason. Although I will use SELECT when I know my SELECT will only return 1 value because I am specifying a WHERE clause against a PRIMARY KEY or UNIQUE INDEX/CONSTRAINT.
Likewise. I use SET for single values as it will throw an error should more than one value (row) be returned -- which is the same error that will be thrown by Oracle (i.e., subquery returns more than one row). SQL Server has this nasty "feature" (sic) where more than one value (row) can be fetched into a single variable without any warning.
May 5, 2009 at 8:20 am
JohnG (5/5/2009)
Jack Corbett (5/5/2009)
Jeffrey Williams (5/4/2009)
I generally use SET myself - mostly because it prevents issues where you can get the wrong value returned from a SELECT. When using SELECT - if you are selecting from a table it can return incorrect results if you are not careful.I also use SET the majority of the time for this reason. Although I will use SELECT when I know my SELECT will only return 1 value because I am specifying a WHERE clause against a PRIMARY KEY or UNIQUE INDEX/CONSTRAINT.
Likewise. I use SET for single values as it will throw an error should more than one value (row) be returned -- which is the same error that will be thrown by Oracle (i.e., subquery returns more than one row). SQL Server has this nasty "feature" (sic) where more than one value (row) can be fetched into a single variable without any warning.
Personally, if I write the query in such a way that it will potentially return multiple rows with different values in that column, then I would go back and rewrite the query.
- 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
May 5, 2009 at 1:15 pm
I'm also in the It Depends camp. One of the determining factors is the source of the value going into the variable. If it's coming from a SELECT to begin with, well... 😀
Viewing 15 posts - 1 through 15 (of 82 total)
You must be logged in to reply to this topic. Login to reply