Select vs Set for assigning values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select vs Set for assigning values

Author

Topic

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-07 : 11:57:25

Hi all,

From a best practices point-of-view, I am trying to determine which is a better standard, or whether there is truly no difference, between using


set @variable = value

and


select @variable = value

I haven't seen any reference to difference in performance of the 2 operations. Since I can assign 2 values in a select, which I typically do, i.e.


declare @error	int
declare @rowc	int
print 'hello'

select * from sysobjects
set @error = @@error
set @rowc = @@rowcount

select * from sysobjects
select @error = @@error,@rowc = @@rowcount

and the access path *seems* to indicate that the single select might be more efficient than the 2 set's, I am leaning that way.

Is there any reference to either of these methods in ANSI standards? That might impact the route I take, alothough of course the @@error and @@rowcount are SQL server specific, so the code isn't really portable without change.

Thoughts ?

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

bmanoj
Starting Member

13 Posts

Posted - 2004-07-07 : 12:06:29

You may want to take a look at [url]http://vyaskn.tripod.com/differences_between_set_and_select.htm[/url]

Manoj

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-07 : 12:18:09

My thanks - I will...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-07 : 12:22:06

Manoj, that's a PERFECT link!

Exactly what I needed to know - anyone interested on this topic, I highly recommend you follow the link!

CiaO

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-07 : 12:37:50

I think its a bit of a bind. I'm probably covering what is in Vyas page, but:

SET is ANSI.

SET ... SET ... SET ... is slower than SELECT ..., ..., ...

You cannot use SET to capture BOTH @@ERROR and @@ROWCOUNT (well you can, but the code is rediculous) - so you have to break the ANSI thingie anyway.

I use stuff like
SELECT @FOO=MAX(MyColumn) FROM MyTable
and you cannot do
SET @FOO=MAX(MyColumn) FROM MyTable
so back to using SELECT again, although you can do
SET @FOO = (SELECT MAX(MyColumn) FROM MyTable)
which has the added benefit of raising error if more than one row is selected - but given that I am checking @@ERROR and @@ROWCOUNT after such statements that's not a huge benefit.

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 12:56:00

Never knew this...


USE Northwind
GO

DECLARE @OrderID int

SELECT @OrderId = OrderId FROM Orders

SELECT @OrderId
GO

DECLARE @OrderID int

SET @OrderId = (SELECT OrderId FROM Orders)

SELECT @OrderId
GO

I always thought it bizzare that SELECT would assign the last result and not through an error....

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-07 : 13:03:20

> I always thought it bizzare that SELECT would assign the last result and not through an error....

That side-effect is a Key Benefit when trying to simulate LIMIT though ...

Then there's the wacky (and quite possibly ANSI for all I know)


UPDATE MyTable
SET    @NextNumber = MyIDColumn = @NextNumber+1

(if I've remembered the syntax correctly)

Kristen

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 13:22:57

What? Another "feature"


USE Northwind 
GO

CREATE TABLE myTable99(Col1 int)
GO

INSERT INTO myTable99(Col1) SELECT 1
GO

SELECT * FROM myTable99
GO

DECLARE @Col1 int
SELECT @Col1 = 0

UPDATE myTable99 SET @Col1 = Col1 = @Col1+1

SELECT * FROM myTable99
SELECT @Col1
GO

DROP TABLE myTable99
GO

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-07 : 13:31:38

The "another feature" I've found is to post something here and the Brett Wizzard converts it into a MyTable99 example!

I bet the MySQL forums don't have a feature like that ...

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-07-07 : 14:56:53

Hey Kristen...

Not that I plan to say I understand that, but, what good is it?

Brett

8-)

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 03:54:41

Its fantastic! People coming to this site looking for solutions to a problem (they DO do a search first, don't they?!) will have a ready-made example to tinker with.

Kristen

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 04:15:02

Kristen,

Based on what I read from the article, my take on it is that unless you need to code a truly ANSI standard system, you'll need to use SET, but that in almost all other occasions we will use select to assign variables. Definitely use SELECT to assign @ERROR and @ROWCOUNT.

BTW, I assume @@error and @@rowcount are not ANSI standard? That would mean that the code is not ANSI standard anyway.

Well, CiaO 4 NoW

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

LarsG
Constraint Violating Yak Guru

284 Posts

Posted - 2004-07-08 : 05:29:01

quote:

BTW, I assume @@error and @@rowcount are not ANSI standard?

That's correct. The PSM (the ANSI term for stored procedures) specification uses the GET DIAGNOSTICS statement and exception handlers for error handling.

E.g.



declare rows int;
declare continue handler for sqlexception
-- will catch all exceptions except warnings and not found excepions
begin
    declare sqlstate char(5);
    get diagnostics exception 1 sqlstate = returned_sqlstate;
    -- take action
end
insert into t values(1,2,3);
-- the exception handler is invoked if error

insert into x select * from t;
get diagnostics rows = row_count;
if rows > 0 then
   -- something inserted
end if

quote:

UPDATE MyTable
SET @NextNumber = MyIDColumn = @NextNumber+1

This is not ANSI compliant.

The limitation that you can only assign one variable with SET is T-SQL only.

ANSI SQL allows for


set (v1,v2) = (1,2);
-- or to select from a table into multiple variables
select c1,c2 into v1,v2 from t where ...; -- Yes the T-SQL variant of select into is not ANSI compliant
-- or
set (v1,v2) = (select c1,c2 from t where ...);
-- In the last example there will be an error if the select returns more than one row.

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2004-07-08 : 05:36:26

quote:

Originally posted by LarsG

That's correct. The PSM (the ANSI term for stored procedures) specification uses the GET DIAGNOSTICS statement and exception handlers for error handling.

E.g.



declare rows int;
declare continue handler for sqlexception
-- will catch all exceptions except warnings and not found excepions
begin
    declare sqlstate char(5);
    get diagnostics exception 1 sqlstate = returned_sqlstate;
    -- take action
end
insert into t values(1,2,3);
-- the exception handler is invoked if error

insert into x select * from t;
get diagnostics rows = row_count;
if rows > 0 then
   -- something inserted
end if

Hmm - is that SQL compliant ?

Get Diagnostics... hmmm, interesting - must go and check

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Kristen
Test

22859 Posts

Posted - 2004-07-08 : 08:40:00

quote:
Originally posted by Wanderer

Based on what I read from the article, my take on it is that unless you need to code a truly ANSI standard system, you'll need to use SET, but that in almost all other occasions we will use select to assign variables. Definitely use SELECT to assign @ERROR and @ROWCOUNT.

Given that I've got to use SELECT for half of the job I decided it was pointless using SET for the other half, so I use SELECT throughout.

Thus SET is only used in UPDATE statements and for options like SET NOCOUNT ...

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-08 : 08:47:59

Same here. It's a freaking waste of time to use SET for all my variables when I have to assign 50 of them also. Even if it is just cut and paste. SELECT is supported. I use it.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-07-08 : 12:07:07

Ha! hey..if MeanOldDBA uses it...I will use it!

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources