SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SELECT vs SET
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 09/25/2005 :  00:47:04  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/25/2005 :  02:04:03  Show Profile  Reply with Quote
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
Go to Top of Page

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 09/25/2005 :  02:24:42  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/25/2005 :  02:37:30  Show Profile  Reply with Quote
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
Go to Top of Page

sachinsamuel
Constraint Violating Yak Guru

India
383 Posts

Posted - 09/25/2005 :  03:49:32  Show Profile  Reply with Quote
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
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 09/25/2005 :  04:02:14  Show Profile  Reply with Quote
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
Go to Top of Page

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 09/25/2005 :  08:33:53  Show Profile  Reply with Quote
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

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/26/2005 :  01:16:24  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/26/2005 :  01:20:05  Show Profile  Reply with Quote
"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
Go to Top of Page

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 09/26/2005 :  21:50:04  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/27/2005 :  03:17:10  Show Profile  Reply with Quote
"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
Go to Top of Page

vivek.kumargupta
Starting Member

India
45 Posts

Posted - 09/29/2005 :  01:56:44  Show Profile  Reply with Quote
yups ...
It can be handy at times and also SET is ANSI compliant


Thanks,
Vivek
Go to Top of Page

Golfnut_1969
Starting Member

USA
2 Posts

Posted - 09/29/2006 :  15:26:46  Show Profile  Reply with Quote
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

Go to Top of Page

Golfnut_1969
Starting Member

USA
2 Posts

Posted - 09/29/2006 :  15:33:05  Show Profile  Reply with Quote
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
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/29/2006 :  18:52:20  Show Profile  Reply with Quote
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
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 09/30/2006 :  04:04:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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
Go to Top of Page

Guennadi Vanine
Starting Member

5 Posts

Posted - 05/02/2007 :  06:00:38  Show Profile  Reply with Quote
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
Go to Top of Page

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 05/02/2007 :  06:11:24  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
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"

Edited by - harsh_athalye on 05/02/2007 06:12:09
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/02/2007 :  09:06:59  Show Profile  Reply with Quote
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



Go to Top of Page

honigkuchenmann
Starting Member

1 Posts

Posted - 08/10/2007 :  11:19:35  Show Profile  Reply with Quote
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!

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.17 seconds. Powered By: Snitz Forums 2000