SQL Server Performance Forum – Threads Archive
using SET versus select
Which is faster? SET @blah = select name from insertedSET @blah2 = select name2 from inserted
..
.. OR
SELECT @blah = name, @blah2 = name2 ….
FROM inserted Thanks!
I tried some unscientific testing, and both came out exactly the same in my tests. So I would conclude that it doesn’t really matter which one you use, from a performance perspective. Has anyone done any testing?
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
True, makes no difference in the execution times and as a general using SELECT is advisable than using SET. HTH Satya SKJ
But aren’t you performing 2 operations instead of 1? Wouldn’t this add up over a lot of statements? Wouldn’t the overhead to prepare many cost more the overhead to prepare 1?
I personally use SELECT because we are in the process of migrating our SQL Server code to Sybase, and it has saved me some work by not using the SET statement.
"How do you expect to beat me when I am forever?"
I don’t know how SQL Server run SET and SELECT internally, but all my testing results in the exact same performance, so somehow the query optimizer must know how to deal with both of these efficiently.
——————
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com
Should be no difference in performance (unless you use a huge amount of SET maybe) but there are some other differences.<br /><br />1.<br />You can only assign a value to one variable at a time with SET but you can assign values to multiple varibles with SELECT.<br /><br />SET @blah1 = 1, @blah2 = 2 does not work<br />SELECT @blah1 = 1, @blah2 = 2 works<br /><br />If you for example want to catch both @@ERROR and @@ROWCOUNT after an update you need to use SELECT. Using two SET statments wouldn’t work since the first SET statment would change the value of @@ERROR.<br /><br />2.<br />SELECT works even if your query returns multiple values but SET don’t. If you have a table "my_table" with multiple rows in it then:<br /><br />"SET @my_variable = (SELECT my_column FROM my_table)" fails.<br /><br />"SELECT @my_variable = my_column FROM my_table" works. (uses first or last row, don’t remember)<br /><br />But then again, maybe you would prefer it to fail if it returned many rows so then you use SET.<br /><br />3.<br />One of them is ANSI standard but I don’t remember which [<img src=’/community/emoticons/emotion-2.gif’ alt=’
‘ />]
SELECT is the ANSI standard
"How do you expect to beat me when I am forever?"
I’ve read that using SELECT when assigning a value to a variable can generate some kind of empty recordset that gets returned to your calling program when calling via ADO although I’ve never experienced any problems myself but it also said that using SET NOCOUNT ON in your stored proc does away with the returning of this ‘null’ recordset. I only use SET and like all of the above havn’t recorded any performance problems between the 2. World Domination Through Superior Software
]]>