May 6, 2009 at 9:38 pm
SET is safer. Code below will throw an error. With select you will get a value from one of the records. Troubleshooting logic error caused by SELECT is more difficult than noticing a crash.
declare @S int
set @S = (select addressid from Person.Address where AddressID < 5)
If you try this:
declare @cnt int
set @cnt = 1
print @cnt
set @cnt = (select 1 where 2=3)
print @cnt
print '-------------------------'
set @cnt = 1
print @cnt
select @cnt = 1 where 2=3
print @cnt
it will print:
1
-------------------------
1
1
With SET you when you did not return the record you got NULL in your variable as expected.
With SELECT you can get into a hard to find logic bug.
And of course if you have many variables to assign and no danger for bugs like above it's a different story.
May 6, 2009 at 10:01 pm
aloj (5/6/2009)
SET is safer. Code below will throw an error. With select you will get a value from one of the records. Troubleshooting logic error caused by SELECT is more difficult than noticing a crash.declare @S int
set @S = (select addressid from Person.Address where AddressID < 5)
If you try this:
declare @cnt int
set @cnt = 1
print @cnt
set @cnt = (select 1 where 2=3)
print @cnt
print '-------------------------'
set @cnt = 1
print @cnt
select @cnt = 1 where 2=3
print @cnt
it will print:
1
-------------------------
1
1
With SET you when you did not return the record you got NULL in your variable as expected.
With SELECT you can get into a hard to find logic bug.
And of course if you have many variables to assign and no danger for bugs like above it's a different story.
"Logic bug"? Heh... what's that? 😉
I'm with Gus... if you do it right, it won't matter one way or the other. Using one or the other to make up for, ummmmm.... buggy code, probably isn't the best practice in the world.
--Jeff Moden
May 6, 2009 at 10:02 pm
SELECT do not change the value if it is not initialized by the statement. Also, if the multiple records returned from the SELECT statement, the value of last recod is set to the variable, and that mostly is confusing. One has to be sure while initializing Variable in SELECT that it must return One record.
Secondly, I think that is the behavior of the SELECT. If the value is not initialzed by the SELECT, the varisble remains unchanged. Whereas in SET, it changes to the returned value or NULL.
May 7, 2009 at 5:58 am
FWIW, I tend to use SELECT the vast majority of the time.
It's more a question of style than substance.
Go with whatever makes you happiest.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 7, 2009 at 7:14 am
Atif Sheikh (5/6/2009)
SELECT do not change the value if it is not initialized by the statement. Also, if the multiple records returned from the SELECT statement, the value of last recod is set to the variable, and that mostly is confusing. One has to be sure while initializing Variable in SELECT that it must return One record.Secondly, I think that is the behavior of the SELECT. If the value is not initialzed by the SELECT, the varisble remains unchanged. Whereas in SET, it changes to the returned value or NULL.
Atif is correct that a Select that returns no value leaves the variable with its previous value, something that has bitten me in sensitive spots on more than one occasion when reusing variables. If you are going to reuse a variable be sure to SET it to NULL or some default value between uses.
There is a good overview of the difference between SET and SELECT here: http://www.sqlmag.com/Article/ArticleID/94555/SELECT_vs_SET_Optimizing_Loops.html (not sure if it's a subscriber article, sorry).
However, you can see the timing differences for yourself.
DECLARE @iOne INT, @iTwo INT, @iThree INT, @iFour INT, @Loop INT
SET @Loop = 1
WHILE @loop < 1000000
BEGIN
SET @iOne = 1;
SET @iTwo = 2;
SET @iThree = 3;
SET @iFour = 4;
SET @Loop = @Loop + 1
END
GO
DECLARE @iOne INT, @iTwo INT, @iThree INT, @iFour INT, @Loop INT
SELECT @Loop = 1
WHILE @Loop < 1000000
BEGIN
SELECT
@iOne = 1,
@iTwo = 2,
@iThree = 3,
@iFour = 4,
@Loop = @Loop + 1;
END
In SSMS run each batch individually with Include Client Staticis set from either the query menu or the icon bar. On my laptop I find that the processing time of the Set version is 2235 with total execution time of 2250. Select was 1031.
EDIT: Be sure to Reset Client Statistics between batches. It's on the Query menu.
Admittedly, this is an artificial setup. After all, how many times are you going to be doing something like this? But it is something to keep in mind if you are going to be reusing one or more variables in a loop or some other iterative process or if you have a lot of variables to initialize.
Cheers,
Don
May 7, 2009 at 9:20 am
"Logic bug"? Heh... what's that?
I'm with Gus... if you do it right, it won't matter one way or the other. Using one or the other to make up for, ummmmm.... buggy code, probably isn't the best practice in the world.
Not worried about whether or not I do it right (or you do it right), more concerned about the person who comes behind and does not realize the pitfalls of using select. One minor change to the query and you could easily get the wrong results - and now you have to spend a lot of time trying to figure out the problem.
It can even be worse, because sometimes what is returned is correct so it becomes an intermittent problem which is even harder to diagnose.
I use both myself, but tend to using SET for most and SELECT when I have multiple variables to be returned from a single query.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 7, 2009 at 10:14 am
Jeffrey Williams (5/7/2009)
Not worried about whether or not I do it right (or you do it right), more concerned about the person who comes behind and does not realize the pitfalls of using select. One minor change to the query and you could easily get the wrong results - and now you have to spend a lot of time trying to figure out the problem.It can even be worse, because sometimes what is returned is correct so it becomes an intermittent problem which is even harder to diagnose.
I use both myself, but tend to using SET for most and SELECT when I have multiple variables to be returned from a single query.
My thoughts exactly. Even if it is coded correctly at the start, things change and you want to catch those "errors" at least during testing.
As a side note, Oracle will NOT allow you to SELECT (via SELECT INTO ...) more than one row into a set of scalar variables.
May 7, 2009 at 10:41 am
JohnG (5/7/2009)
Jeffrey Williams (5/7/2009)
Not worried about whether or not I do it right (or you do it right), more concerned about the person who comes behind and does not realize the pitfalls of using select. One minor change to the query and you could easily get the wrong results - and now you have to spend a lot of time trying to figure out the problem.It can even be worse, because sometimes what is returned is correct so it becomes an intermittent problem which is even harder to diagnose.
I use both myself, but tend to using SET for most and SELECT when I have multiple variables to be returned from a single query.
My thoughts exactly. Even if it is coded correctly at the start, things change and you want to catch those "errors" at least during testing.
As a side note, Oracle will NOT allow you to SELECT (via SELECT INTO ...) more than one row into a set of scalar variables.
Heh... yet another reason for me to not like Oracle. There are times when doing such a thing is very convenient and I'd prefer it if the system didn't try to protect me so much.
--Jeff Moden
May 7, 2009 at 12:03 pm
Yes, there are disadvantages to using Select to assign values, if you use it incorrectly.
On the other hand, if I limited myself to things that can only, ever be used correctly and have no chance of misuse, because of some nebulous fear that someone coming along after me would do it wrong, I'd pretty much have to eliminate ever doing anything again, including eating or drinking. (Not sure how someone could do breathing wrong, but food can definitely be messed up.)
To me, that's kind of like never driving to work, because the guy on the road behind you might not know what turn signals mean, might not know how to use the break pedal, and so on.
I've been asked at times to rewrite efficient, effective code that gets the right results, into less efficient, less effective code, so that people who haven't bothered to learn T-SQL correctly will be able to "maintain it". To me, that's like asking the contractor who's building your house to not use electrical wiring, because you aren't an electrician. When my boss asks for it, I do it his way, because it's in his authority to ask that, but I hate having to.
- 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 7, 2009 at 7:09 pm
I see it this way: if somebody asks what is better to use I would say SET because "you/another person after you/some seemingly unrelated change in the database" are less likely to create hard to detect bug.
Domain redesign can change how many records the query returns for example.
As there is defensive driving there is also defensive coding.
I am not saying that SELECT is bad but if you do not have valid reason not to use SET, you probbably better off using SET and advising people, who ask what is better, to prefer SET over SELECT.
You might be the person spending better part of the night debugging the application that sometimes "magically" inserts wrong data regardless of the fact who did it, so, just in self-defense :-).
May 7, 2009 at 7:57 pm
I believe is SET is best option than SELECT.. for over all occasion.
-swayambhu
MCAD,MCDBA,
May 8, 2009 at 9:03 am
Thanks for all the different perspectives.
I didn't know that a SELECT that returns multiple rows would give you the last value. It sounds a bit scary to rely on returning the first/last value as someone could change around your table structure, and mess you up bad, and who'd want to put an order by on a query to set a variable. As others have said, syntax should not "fix" crappy logic.
Thanks for the chuckle gsquared...
GSquared (5/7/2009)
When my boss asks for it, I do it his way, because it's in his authority to ask that, but I hate having to.
I doubt that I will use SELECT any more than I have in the past, but now I know my risks that come along with the improved performance.
May 8, 2009 at 10:03 am
As an aside, and for general amusement rather than as a serious proposal, note that the following will ensure that an error is raised if you try to assign more than one value to a variable:
DECLARE @T TABLE (int_value INT)
INSERT @T SELECT 1 UNION ALL SELECT 2
DELCARE @i INT;
SELECT @i = int_value
FROM dbo.TableWithDuplicates AS T
ORDER BY (1 / (ROW_NUMBER() OVER (ORDER BY (SELECT 1)) - 2));
😀 Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 8, 2009 at 10:38 am
Paul, you're a very spooky man... :crazy:
:laugh:!
How to Post Data/Code to get the best Help How to Post Performance Problems
May 8, 2009 at 10:40 am
Heh. Actually, SET is no more safe than SELECT, at least for the examples given here that I have seen so far.
If you look at this code:
Declare @i as int
Set @i = (select object_id from sys.objects)
Select @i = (select object_id from sys.objects)
You will note that BOTH SET and SELECT return the same errors.
Likewise, if you try this code:
Declare @i as int
Set @i = (select object_id from sys.objects where name='kjkj')
select @i
Select @i = (select object_id from sys.objects where name='kjkj')
select @i
You will note that both SET and SELECT happily leave @I as NULL without any warnings.
[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]
Viewing 15 posts - 16 through 30 (of 82 total)
You must be logged in to reply to this topic. Login to reply