Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: SET vs SELECT

  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: SET vs SELECT

    What should be used when assigning values to variables in T-SQL, SET or SELECT?
    Code:
    DECLARE @Return datetime
    
    --THIS
    SELECT @Return = CONVERT(datetime, CONVERT(varchar(10),'07/01/2007',120) + ' 00:00:00')
    
    --OR THIS?
    SET @Return = CONVERT(datetime, CONVERT(varchar(10),'07/01/2007',120) + ' 00:00:00')
    What is the better method (speed/performance, compliance, etc)?

    Just a question that's been knocking around in my head for a while
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    BOL says: "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable."

    Doesn't say why, though.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hehe, I thought that was the case (googling gave me that answer more often) but I would like to know why!

    I read somewhere that SELECT is actually faster but SET is the standard...
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    SET or SELECT ... INTO ... (not the above "SELECT @var = ...") can be used in SQL to set variables. Whichever one is faster depends on the specific DBMS product and should be discussed in the respective (other) forum.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ok then, so I notice that the thread has been moved as a MSSQL Server topic, so here's a bump

    Which should I be using?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Depends. They are different

    SET is ansi, SELECT is not. SET will error if you attempt to assign it to a query that returns more than one value, SELECT will not. SET allows to assign only one variable a value at a time, SELECT many.

    http://vyaskn.tripod.com/differences...and_select.htm
    Otherwise try googling - there are loads of resources out there.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh yes - for speed set tends to be better if you set one variable. If you need to do multi variable assignment, select is better than n sets. AS I recall....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Happy Monday morning Poots!
    Cheers for the resources, I have added the link to my bookmarks and I will give it a read soon!
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Good morning George and thank you although you are clearly much more excited that the weekend is over than I
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    SET - "standard"
    Books Online state: "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable.

    SET - single variable assignment
    SELECT - multiple variable assignment
    Eg1: Syntax
    Code:
    DECLARE @Variable1 AS int, @Variable2 AS int
    SET @Variable1 = 1
    SET @Variable2 = 2
    SELECT @Variable1 = 1, @Variable2 = 2
    Eg2: Subselect
    Code:
    DECLARE @Test varchar(10)
    SET @Test = (SELECT Forename FROM Employees WHERE Surname = 'Smith')
    SELECT @Test
    --This code will fail if the subselect returns more than one value
    SELECT @Test = (SELECT Forename FROM Employees WHERE Surname = 'Smith')
    SELECT @Test
    -- This code will not fail if the subselect returns more than one value
    -- However, @Test will only be assigned one value which may not be what you expect
    Speed: SET > SELECT with multiple variable assignments in a loop.
    George
    Home | Blog

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Good morning George and thank you although you are clearly much more excited that the weekend is over than I
    Not really, just trying to put a positive spin on the morning!
    I spent my weekend lying underneath the cars for some pre-MOT fun and then decorating the basement rooms... Oh joy
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    -- However, @Test will only be assigned one value which may not be what you expect
    It will always be what you expect if you use an order by clause.


    For example a bit of pared down code I use because I am too lazy to learn cursor syntax.
    Code:
     
    DECLARE @db_name AS SYSNAME
    -----------------------------------------------------------------------------
    --  NOTE - there can be no other statements between this and the loop.
    -----------------------------------------------------------------------------
    SELECT @db_name = name
    FROM sys.databases
    WHERE db_id(name) NOT IN(1, 3, 4)
    ORDER BY name DESC
     
    --While there are still dbs to act upon....
    WHILE @@ROWCOUNT <> 0 BEGIN--db loop
     
     --Do stuff
     
    -----------------------------------------------------------------------------
    --   NOTE - there can be no other statements between this and the END of loop.
    -----------------------------------------------------------------------------
     --Get the next db
     SELECT @db_name = name
     FROM sys.databases
     WHERE name > @db_name
     --Ignore Model, MSdb and Master
       AND db_id(name) NOT IN(1, 3, 4)
     ORDER BY name DESC
     
    END--db loop
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by georgev
    -- However, @Test will only be assigned one value which may not be what you expect
    Quote Originally Posted by pootle flump
    It will always be what you expect if you use an order by clause.
    Very true.
    And as for cursor syntax - I've yet to even delve into complex CREATE statements, let alone cursors
    George
    Home | Blog

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    SELECT is inherently a set-based operation, that's what allows all the wiggle room with multi variable assignments and such. SET is a scalar assignment.

    Keep in mind that a variable assignment in a SELECT statement is executed once for each record returned as well. This can lead to unforeseen performance hits. However, you can use this behavior to your advantage as well...

    Example:

    Code:
    DECLARE @VARTEST VARCHAR(4000)
    
    CREATE TABLE #tblTest (testfield char(1))
    
    INSERT INTO #tblTest VALUES (1)
    INSERT INTO #tblTest VALUES (2)
    INSERT INTO #tblTest VALUES (3)
    INSERT INTO #tblTest VALUES (4)
    INSERT INTO #tblTest VALUES (5)
    
    SELECT @VARTEST = COALESCE(@VARTEST, '') + testfield + ', ' FROM #tblTest
    
    SELECT 'vartest = ' + @VARTEST
    
    DROP TABLE #tblTest
    Last edited by Teddy; 04-24-07 at 17:43.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *nods in appreciation*
    That's clever!

    So they both have their advantages (and disadvantages) and both have their place. I don't think I can pick one to use as a standard, it depends on the context.

    That's not very clear, but I know what I mean
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •