SET vs SELECT

Question

SET vs SELECT

Viewing 15 posts - 31 through 45 (of 82 total)

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

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 1

Up until 8 years ago; I was a software engineer and software development manager regionally and I had few rules in the department. Among those rules there were 2 of them that were kind of important.

1> code portability, because of the systems and software shelf-life and change in technology

2> ease of reading the top-down designs and various different application codes.

Thus, to my opinion, just mine, SET wins over Select; since there is no major performance hit chosing one over the other.. except that we are always trying to find the way to make the code produced faster.. right? Right! Like, not documenting our codes.. :hehe:

These are just my opinion and I have been wrong before!!

Cheers,
John Esraelo

Paul White SSC Guru Points: 150467 More actions · Answer 2

John,

When assigning multiple variables from constants, SELECT is significantly quicker than separate SET statements.

Just adding that for completeness.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 3

Florian Reischl (5/8/2009)
Paul, you're a very spooky man... :crazy:
:laugh:!

I am?

I am!

Were you about to post something similar? Because I claim prior art!

See http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/t-sql-variables-multiple-value-assignment.aspx

My comment is at the very bottom of the blog.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 4

RBarryYoung,

How does that go with:

Select @i = object_id from sys.objects

...?

Paul

(a confirmed SELECT-ite, mostly)

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

John Esraelo-498130 SSCertifiable Points: 5914 More actions · Answer 5

John Esraelo-498130

SSCertifiable

Points: 5914

May 8, 2009 at 11:43 am

#991350

I type faster ... .

😛

Cheers,
John Esraelo

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

Paul White (5/8/2009)
RBarryYoung,
How does that go with:
Select @i = object_id from sys.objects
...?
Paul
(a confirmed SELECT-ite, mostly)

Not sure what you're asking? That does behave differently, but then it should because it's coded differently.

My point is, if you take most of these "SET is safer" examples (the ones I've tried so far, anyway) and just replace the SET with SELECT, they actually seem to behave the same. Most of the "SELECT is unsafe" examples have actually been rewritten to use the shorter, more direct syntax available to SELECT, but it actually changes it's behavior also. They're not really comparing apples to apples.

As far as I can tell, it's perfectly easy to use SELECT as safely as SET.

[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]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

Barry,

Yes your examples are actually the "safer" version of using select. The issue I have, and I use both Set and Select is that

Select @i = object_from sys.all_objects

Will work and return a value to @i, but

Set/Select @i = (Select object_id from sys.all_objects)

Will return an error.

Edit: Sorry Barry, I actually misread your last post and we are agreeing. I should have known better than to act like I disagreed.

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

Paul White SSC Guru Points: 150467 More actions · Answer 8

Darth,

That was my attempt to highlight the difference between the posted examples, and the only actually 'dangerous' one I can think of (which I posted).

As you say, it is just as easy to use SELECT safely, but it's a matter of personal taste I guess.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150467 More actions · Answer 9

Jack,

The three of us are agreeing - though the casual observer might be hard pressed to spot it :laugh:

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jack Corbett SSC Guru Points: 184394 More actions · Answer 10

Paul White (5/8/2009)
Jack,
The three of us are agreeing - though the casual observer might be hard pressed to spot it :laugh:
Paul

No doubt as it took me a minute to figure out we were agreeing 😀 Glad we are all agreeable despite Barry being the Darth Vader of SSC.:w00t:

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

Paul White SSC Guru Points: 150467 More actions · Answer 11

Jack Corbett (5/8/2009)
Glad we are all agreeable despite Barry being the Darth Vader of SSC.:w00t:

I take the view that it is safest, on balance, to agree with people who are able to strangle others remotely 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Jack Corbett SSC Guru Points: 184394 More actions · Answer 12

So maybe the despite should be because. 😛

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

*sigh* I'm so misunderstood. 🙁

[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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Actually, I'm more in the ambivalent camp myself. I doubt that I use either one enough in stored procedures for the performance difference to ever really matter, and if I init any variables it's usually to constants/literals anyway. That's because 1) I prefer to have look-ups like that as part of larger queries and 2) I tend to think of the number of variables in a routine as a rough measure of it's "procedural-ness" anyway, and thus, something to be avoided (in SQL) when possible and reasonable.

[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]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 15

Paul White (5/8/2009)
Florian Reischl (5/8/2009)
Paul, you're a very spooky man... :crazy:
:laugh:!
I am?
I am!
Were you about to post something similar? Because I claim prior art!
See http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/t-sql-variables-multiple-value-assignment.aspx
My comment is at the very bottom of the blog.
Paul

I think I misunderstood your code and the intention. This is a really nice deal if you want to be sure that there is no other value. My thought was much more simple. To ensure that you get only one (the first value of a SELECT) I just would suggest a TOP. My mistake.

Greets

Flo