RSS 2.0 Feed
RSS 2.0


Atom 1.0 Feed
Atom 1.0

  T-SQL: SET vs SELECT when assigning variables 


Do you know what the difference is between using SET and SELECT when assigning varaibles in T-SQL? Well, there are some differences. I came across a great article by Narayana Vyas Kondreddi from the UK that describes the difference between the two.

The article is well worth the read, but here are the main points:

  1. SET is the ANSI standard for variable assignment, SELECT is not.
  2. SET can only assign one variable at a time, SELECT can make multiple assignments at once.
  3. If assigning from a query, SET can only assign a scalar value. If the query returns multiple values/rows then SET will raise an error. SELECT will assign one of the values to the variable and hide the fact that multiple values were returned (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)
  4. When assigning from a query if there is no value returned then SET will assign NULL, where SELECT will not make the assignment at all (so the variable will not be changed from it's previous value)
  5. As far as speed differences - there are no direct differences between SET and SELECT. However SELECT's ability to make multiple assignments in one shot does give it a slight speed advantage over SET.

Take a look at the complete article to see Vyas' complete tests to get the entire picture:
[http://vyaskn.tripod.com/differences_between_set_and_select.htm]




                   



Leave a comment below.

Comments

  1. Jason 1/1/2005 11:09 PM
    Gravatar
    Thank you!
  2. Gary 11/8/2005 3:00 AM
    Gravatar
    Thanks for this post this is exactly what I was looking for. It seems you and I had the same Idea for naming of Blogs :-)
  3. Sean 3/15/2006 10:59 AM
    Gravatar
    Just got a new job as SQL Server DBA...very helpful....
  4. Zahid Nawaz Kashmala Fatima 5/19/2006 7:38 AM
    Gravatar
    Good worthy Article
  5. Rajasekhar 7/18/2006 7:24 AM
    Gravatar
    Looking for the same, Thanks for the post
  6. Gomathi 7/20/2006 12:56 AM
    Gravatar
    Thanks for your information and article link.
  7. Alex 8/11/2006 1:39 PM
    Gravatar
    Is this also the case for SQL 2000, or just 2005?
  8. Ryan Farley 8/11/2006 1:44 PM
    Gravatar
    Alex,

    This is the case for T-SQL, makes no difference if it is 2000 or 2005.

    -Ryan
  9. simon 2/15/2007 7:20 AM
    Gravatar
    -- Sorry but the following code shows that
    -- (A) SET and SELECT are IDENTICAL IN THEIR BEHAVIOUR
    -- (B1) When assigning from a query IF THE QUERY RETURNS MULTIPLE ROWS the variable is unchanged (and an error occurs)
    -- (B1) When assigning from a query IF THE QUERY RETURNS NO ROWS the variable is set to NULL and no error occurs

    alter procedure
    smSetAndSelectTest as

    --exec smSetAndSelectTest
    declare @VAR varchar(80)

    --see http://ryanfarley.com/blog/archive/2004/03/01/390.aspx

    --He says

    -- If assigning from a query, SET can only assign a scalar value.
    -- If the query returns multiple values/rows then SET will raise an error.
    -- SELECT will assign one of the values to the variable and hide the fact that multiple values were returned
    -- (so you'd likely never know why something was going wrong elsewhere - have fun troubleshooting that one)"
    -- When assigning from a query if there is no value returned then
    -- SET will assign NULL, where SELECT will not make the assignment at all
    -- (so the variable will not be changed from it's previous value

    -- The following code shows that
    -- (A) SET and SELECT are IDENTICAL IN THEIR BEHAVIOUR
    -- (B1) When assigning from a query IF THE QUERY RETURNS MULTIPLE ROWS the variable is unchanged (and an error occurs)
    -- (B1) When assigning from a query IF THE QUERY RETURNS NO ROWS the variable is set to NULL and no error occurs




    print isNULL(@var,'@var is NULL')
    SET @var ='SET and SELECT test'
    print isNULL(@var,'@var is NULL')

    SET @var ='(1a) SELECT NULL value assignment test'
    print isNULL(@var,'@var is NULL')
    SELECT @var = NULL
    print isNULL(@var,'@var is NULL')

    SET @var ='(1b) SET NULL value assignment test'
    print isNULL(@var,'@var is NULL')
    SET @var = NULL
    print isNULL(@var,'@var is NULL')

    print '-- SELECT @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------'
    SET @var ='(2a) Select Multiple rows value assignment test'
    print isNULL(@var,'@var is NULL')
    SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
    print isNULL(@var,'@var is NULL')

    print '-- SET @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------'
    SET @var ='(2b) Set Multiple rows value assignment test'
    print isNULL(@var,'@var is NULL')
    SET @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
    print isNULL(@var,'@var is NULL')


    SET @var ='(3a) Single row SELECT assignation best practice'
    print isNULL(@var,'@var is NULL')
    SELECT @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
    print isNULL(@var,'@var is NULL')

    SET @var ='(3b) Single row SET assignation best practice'
    print isNULL(@var,'@var is NULL')
    SET @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')
    print isNULL(@var,'@var is NULL')


    print '-- SET AND SELECT @VAR is NULL WHERE NO ROWS RETURNED -----------------'
    SET @var ='(4a) Select value assignment test (no rows returned)'
    print isNULL(@var,'@var is NULL')
    SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where 1=2)
    print isNULL(@var,'@var is NULL')

    SET @var ='(4b) Set value assignment test (no rows returned)'
    print isNULL(@var,'@var is NULL')
    SET @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where 1=2)
    print isNULL(@var,'@var is NULL')

    SET @var ='(5a) Best Practice value assignment test (no rows returned)'
    print isNULL(@var,'@var is NULL')
    SELECT @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where 1=2)
    print isNULL(@var,'@var is NULL')

    SET @var ='(5b) Best Practice value assignment test (no rows returned)'
    print isNULL(@var,'@var is NULL')
    SET @var = (select max(fin_year) from tikkadev.olapdb.dbo.annual_indicators where 1=2)
    print isNULL(@var,'@var is NULL')
  10. { public virtual blog; } 2/15/2007 10:20 AM
    Gravatar
  11. Gravatar
    -- Here is the output

    @var is NULL
    SET and SELECT test
    (1a) SELECT NULL value assignment test
    @var is NULL
    (1b) SET NULL value assignment test
    @var is NULL
    -- SELECT @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------
    (2a) Select Multiple rows value assignment test
    Server: Msg 512, Level 16, State 1, Procedure smSetAndSelectTest, Line 44
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    (2a) Select Multiple rows value assignment test
    -- SET @VAR is NOT CHANGED ON MULTIROW RETURN -------------------------
    (2b) Set Multiple rows value assignment test
    Server: Msg 512, Level 16, State 1, Procedure smSetAndSelectTest, Line 50
    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
    (2b) Set Multiple rows value assignment test
    (3a) Single row SELECT assignation best practice
    2006
    (3b) Single row SET assignation best practice
    2006
    -- SET AND SELECT @VAR is NULL WHERE NO ROWS RETURNED -----------------
    (4a) Select value assignment test (no rows returned)
    @var is NULL
    (4b) Set value assignment test (no rows returned)
    @var is NULL
    (5a) Best Practice value assignment test (no rows returned)
    @var is NULL
    (5b) Best Practice value assignment test (no rows returned)
    @var is NULL
  12. Joon 5/23/2007 1:28 AM
    Gravatar
    Your multi-row errors are because your statement was:

    SELECT @var = (select fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006')


    And not

    select @var = fin_year from tikkadev.olapdb.dbo.annual_indicators where fin_year = '2006'

    This is the situation that Vyas was talking about in the link article, and is the one that will assign the first or last (I cannot remember which) value
  13. host 6/13/2007 11:29 AM
    Gravatar
    Thats alot of code. Give me some time to assimilate all that
  14. Jshirk 7/12/2007 9:44 AM
    Gravatar
    i love this spiffy split function, which takes a string and returns a table variable. However, I have not been successful at using it to build a table of all words the function returns for all rows in a table. I tried WHILE EXISTS(Select...) but have not gotten it to work.

    basic idea (which is not syntactically correct):

    while exists (select @str=description from item)
    begin
    insert @words
    select fn_split(@str,'@delim')
    end

    Any joy? Thanks!
  15. Jshirk 7/13/2007 7:43 AM
    Gravatar
    FYI here is my solution; it works!

    declare @words TABLE (word varchar(40))
    declare @s varchar(80)
    DECLARE Description_Cursor CURSOR
    FOR
    SELECT description FROM item
    OPEN Description_Cursor
    FETCH FROM Description_Cursor INTO @s
    insert @words select word from dbo.GETALLWORDS(@s, ' ,."') --ORDER BY word

    FETCH NEXT FROM Description_Cursor INTO @s
    WHILE @@Fetch_Status = 0
    BEGIN
    insert @words select word from dbo.GETALLWORDS(@s, ' ,."') --ORDER BY word
    FETCH NEXT FROM Description_Cursor INTO @s
    END
    CLOSE Description_Cursor
    DEALLOCATE Description_Cursor
    select distinct * from @words order by word
  16. Davis 10/1/2007 10:50 AM
    Gravatar
    You should really create a function to do this and keep this function in your stable of code. Found this one on the web. I did a cursory review of it and think it is sound (no warranty or certification this code works all the time). I have seen different functions for different values to be returned (char strings, ints, guids, etc.) so you do not have to convert anything.
  17. Davis 10/1/2007 10:51 AM
    Gravatar
    I don't see the URL I pasted in my comments, so here it is:
    http://blogs.vandamme.com/development/2007/06/parse_comma_sep.html
  18. sanjeev sharma 4/19/2008 10:58 PM
    Gravatar
    SET vs SELECT - Sql server

    We always get confused between SELECT and SET when assigning values to variables, and make mistakes. Here in this article, I will try to highlight all the major differences between SET and SELECT, and things you should be aware of, when using either SET or SELECT

    http://sanjevsharma.blogspot.com/2008/04/set-vs-select-sql-server.html
  19. Johnny 6/2/2008 4:20 PM
    Gravatar
    Great site, alot of code to take in.
    http://www.sqlset.com
  20. Abhishek M 6/18/2008 10:58 PM
    Gravatar
    Thanks.. That was very helpful to make choice between SET and SELECT..
  21. John 7/8/2008 12:34 PM
    Gravatar
    Very useful, great site.
    http://www.sqlset.com
  22. JC 3/24/2009 3:37 AM
    Gravatar
    Well done!
  23. getusama 7/1/2009 5:09 AM
    Gravatar
    Thank you so much. I was looking for exact answer to a weird problem I was facing with cursors.
  24. 10/26/2011 8:31 AM
    Gravatar
    difference is between using SET and SELECT when assigning varaibles in T-SQL? - dBforums
  25. 11/17/2013 9:07 AM
    Gravatar
    SET vs. SELECT &amp;#8211; What&amp;#8217;s the difference? | Ask Programming &amp;amp; Technology
Comments have been closed on this topic.



 

News


Also see my CRM Developer blog

Connect:              


Sponsor

Sections