SQL Server Performance

using SET versus select

Discussion in 'T-SQL Performance Tuning for Developers' started by sql777, Dec 28, 2002.

  1. sql777 New Member

    Which is faster?

    SET @blah = select name from inserted
    SET @blah2 = select name2 from inserted
    ..
    ..

    OR
    SELECT @blah = name, @blah2 = name2 ....
    FROM inserted

    Thanks!
  2. bradmcgehee New Member

    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
  3. satya Moderator

    True, makes no difference in the execution times and as a general using SELECT is advisable than using SET.

    HTH

    Satya SKJ
  4. x002548 New Member

    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?
  5. royv New Member

    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?"
  6. bradmcgehee New Member

    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
  7. Argyle New Member

    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=':D' />]
  8. royv New Member

    SELECT is the ANSI standard


    "How do you expect to beat me when I am forever?"
  9. trifunk New Member

    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

Share This Page