SELECT vs SET - SQL Server Forums

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

SQL Server Development (2000)

SELECT vs SET

Author

Topic

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-09-25 : 00:47:04

What is the difference between the SELECT and SET stmts in SQL Server 2000.What exactly is the purpose of SET stmt when almost everything can be done via SELECT stmt?Is there any instance when SELECT fails and SET works.[font=Arial][blue]

Thanks in Advance.
Vivek

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-25 : 02:04:03

select will actually return a value. Set just sets a value.

I only use select when I actually need to return data, I use SET everywhere else.

-ec

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-09-25 : 02:24:42

As in
declare @var int
set @var =1
go
declare @var int
select @var =1

--Nothing is returned actually ... thus set and select works same as far as above assignment is considered.
My point is , there should be some redundancy in using SET than SELECT which i don't know...
Hope i am being clear.

Thanks,
Vivek

Kristen
Test

22859 Posts

Posted - 2005-09-25 : 02:37:30

If you need to do


SET @Var1 ='foo'
SET @Var2 ='bar'
...

you would be better off with


SELECT @Var1 ='foo',
       @Var2 ='bar',
       ...

because it is faster.

I would prefer to use SET for setting @Variables because it would immediately imply to me the intent (i.e. I'm not selecting a recordset).

However, because multi-variable SET'ting is slower than using SELECTs I need to use SELECT some of the time, and a mixture is inconsistent, so I always use SELECT

Pity really.

Kristen

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-09-25 : 03:49:32

Vivek,

If you want to assign multiple variable in just one statement then use select else use Set.

For e.g

DECLARE @ERR INT
DECLARE @ROWS_AFFECTED INT

UPDATE TEST SET NAME = 'SACHIN SAMUEL' WHERE ID=8

SELECT @ERR=@@ERROR, @ROWS_AFFECTED=@@ROWCOUNT
-- In the above line I am assigning 2 variables in just one line.

Hope I am making sence!

Regards
Sachin Samuel

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-09-25 : 04:02:14

quote:
Originally posted by vivek.kumargupta

As in
declare @var int
set @var =1
go
declare @var int
select @var =1

--Nothing is returned actually ... thus set and select works same as far as above assignment is considered.
My point is , there should be some redundancy in using SET than SELECT which i don't know...
Hope i am being clear.

Thanks,
Vivek

I must have been on crack when i said that. I could have sworn there was some funky difference like this between SET and SELECT, but I appear to be wrong.

Anyway, I found the definitive answer to this question, thanks to Vyas. http://vyaskn.tripod.com/differences_between_set_and_select.htm

-ec

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-09-25 : 08:33:53

Thanks Sachin for an instance where SELECT is handy and also to ec for an informative link.The link gives an exaustive comparisons between the two.

-Vivek

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 01:16:24

I once had this doubt and cleared it by reading Vyas article

If you want to assign a value taking from a table, you can use

Set @var=(Select value from yourTable)

That will work as long as the query returns single value. Otherwise you will get error
But using Select is somewhat safer as you wont get error but the last value

Select @var=value from yourTable

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2005-09-26 : 01:20:05

"I found the definitive answer to this question, thanks to Vyas"

Thanks for the 5-star link ec

Its a while since I read that article, and its reminded me of a couple of things I thought useful the first time I read it but have failed to adopt. I'll try again!

Also the fact that its very hard to do:

SELECT @MyErrNo = @@ERROR, @MyRowCount = @@ROWCOUNT

using SET !

Shame really ...

Kristen

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-09-26 : 21:50:04

So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!

:-)

-Vivek

Kristen
Test

22859 Posts

Posted - 2005-09-27 : 03:17:10

"I think MSFT must have done away with this SET at all"

SET is the ANSI standards compliant way to assign a value to a variable ... but SELECT is "better" in SQL Server, as there are a few things you just cannot do with SET - like capturing @@ROWCOUNT and @@ERROR after a statement - and for multi-variable assignments SELECT is faster.

However, as Vyas's article points out there are some things SET will do better - like raising an error if you attempt to assign multiple values to variable - whereas SELECT @foo = bar FROM MyVeryLargeTable will keep assigning the values until there are no more!

Kristen

vivek.kumargupta
Starting Member

45 Posts

Posted - 2005-09-29 : 01:56:44

yups ...
It can be handy at times and also SET is ANSI compliant

Thanks,
Vivek

Golfnut_1969
Starting Member

2 Posts

Posted - 2006-09-29 : 15:26:46

quote:
Originally posted by vivek.kumargupta

So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!

:-)

-Vivek

Golfnut_1969
Starting Member

2 Posts

Posted - 2006-09-29 : 15:33:05

quote]Originally posted by vivek.kumargupta

So finally the conclusion is that SELECT is the right choice all the times be it performance or programming wise ...
But does that mean that SET was only created by MSFT to assign values to a single variable??I think MSFT must have done away with this SET at all!!!

:-)

-Vivek
[/quote]
Sorry, my original reply got lost.

I recommend using set simply because SELECT will sometime result in previous values. SELECT will not set a previous set variable's values to NULL if no value is returned in the SELECT statement. The SET Statement will. I have found this but in multiple places over the year and I have found that most people are not aware of it. Try the following SQL using the Northwind database. There are 9 records in the Employees table. I will loop through the records setting a variable equal to the first name. Then, once the value is set I will print the value. However, I will loop through the table 14 times, incrementing the EmployeeID each time. Once I get past 9 my select statement will not return a record yet the name value will still print the first name of Employee 9

USE Northwind

DECLARE @Counter int

DECLARE @EmployeeName varchar(10)

SET @Counter = 1

WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SELECT
@EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter

PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')

SET @Counter = @Counter + 1
END

Kristen
Test

22859 Posts

Posted - 2006-09-29 : 18:52:20

I don't see how SET helps there - the FirstName column might be NULL anyway! and thus not distinguishable from "missing" / "logic broken"

Personally I would check @@ROWCOUNT if my requirement was that a row existed in the resultset - and presumably the requirement would be for one row, in which case I would test that @@ROWCOUNT = 1

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-30 : 04:04:09

>>I recommend using set simply because SELECT will sometime result in previous values.

How?

Read my first reply

Madhivanan

Failing to plan is Planning to fail

Guennadi Vanine
Starting Member

5 Posts

Posted - 2007-05-02 : 06:00:38

quote:
[i]Originally posted by Kristen

Personally I would check @@ROWCOUNT if my requirement was that a row existed in the resultset - and presumably the requirement would be for one row, in which case I would test that @@ROWCOUNT = 1

Inserting
print @@rowcount
before
END
results in
@EmployeeName = Nancy
1
@EmployeeName = Andrew
1
@EmployeeName = Janet
1
@EmployeeName = Margaret
1
@EmployeeName = Steven
1
@EmployeeName = Michael
1
@EmployeeName = Robert
1
@EmployeeName = Laura
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1
@EmployeeName = Anne
1

So, checking @@rowcount is of no use

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 06:11:24

Guennadi,

You need to check @@ROWCOUNT immediately after SELECT statement, not at the end of Loop:

DECLARE @Counter int
DECLARE @EmployeeName varchar(10)

SET @Counter = 1

WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SELECT 
@EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter

Print @@ROWCOUNT
PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL') 

SET @Counter = @Counter + 1
END

So, checking @@ROWCOUNT is of the use !

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-02 : 09:06:59

quote:
Originally posted by Golfnut_1969

I will loop through the records setting a variable equal to the first name.

Loop?

Anyway, here are some numbers

http://weblogs.sqlteam.com/brettk/archive/2007/02/12/60090.aspx

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

honigkuchenmann
Starting Member

1 Post

Posted - 2007-08-10 : 11:19:35

really nice to see the difference between SET and SELECT. especially that with nothing returned from the Select statement the previous value is kept..

USE Northwind

DECLARE @Counter AS int
DECLARE @EmployeeName AS varchar(10)

SET @Counter = 1

WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
Select @EmployeeName = FirstName
FROM Employees
WHERE EmployeeID = @Counter

PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END

PRINT '------------Select vs. SET--------------------'

SET @Counter = 1

WHILE @Counter < 15 --There are 9 records in this table, EmployeeID 1-9
BEGIN
SET @EmployeeName = (Select FirstName
FROM Employees
WHERE EmployeeID = @Counter)

PRINT '@EmployeeName = ' + IsNull(@EmployeeName,'NULL')
SET @Counter = @Counter + 1
END

thanks Gulfnut!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources