SQL Server Performance Forum – Threads Archive

Select Vs Set

Method1 Declare @s varchar(4)
Select @s=’Test’
Select @s Method2 Declare @s varchar(4)
Set @s=’Test’
Select @s Both gives same answer
Is there any significant difference between select and set in Dynamic SQL?
Madhivanan Failing to plan is Planning to fail

---

http://vyaskn.tripod.com/differences_between_set_and_select.htm
–Make sure you read completely. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

From BOL
It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.

---

The main advantage of using SET in my eyes, is that you are expecting only 1 row/result from your query. If you use SELECT and your query somehow returns more than 1 result then an error will not occur and it will just take the first value from the result set. On the other hand a SET statement will ERROR if there is more than 1 value in your query. e.g SET @a = (select name from authors where lastname = ‘hilditch’) etc – this will error if there is more than one possible Hilditch in the authors table. If you use SELECT it will not error. There are advantages and disadvantages to both of these but I prefer SET where it errors as otherwise you are most likely hiding unexpected behaviour. Dave. [email protected]
www.matiogi.com

---

Hey Dave, did you read the article? [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]<br /><br />MeanOldDBA<br />[email protected]<br /><br />When life gives you a lemon, fire the DBA.

---

LOL – now I have. Good article – I like the bit on the speed boost for assigning to multiple variables at once using Select – not that I have loops like this in my code much anyway but could come in handy at some point in the future. [email protected]
www.matiogi.com

---

]]>