Select vs Set Local Variable

  • I like the question and the order by concern has already been pointed out. The part of the explanation that really bothers me is this: "It is recommended that you use SET @local_variable instead of SELECT @local_variable". I have never seen anywhere that officially recommends using SET instead of SELECT for setting variable values. It is important to understand the differences and nuances but they both their place.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (3/31/2016)


    I like the question and the order by concern has already been pointed out. The part of the explanation that really bothers me is this: "It is recommended that you use SET @local_variable instead of SELECT @local_variable". I have never seen anywhere that officially recommends using SET instead of SELECT for setting variable values. It is important to understand the differences and nuances but they both their place.

    I agree that they both do have their place. One of the points of the question was for people to think about when they would use set vs. select and the nuances of each. The statement itself was from the MSDN article where it recommended the use of SET instead of SELECT.

  • Thanks for posing the question!

    It seems SELECT is quite a bit more forgiving than SET. Changing the code in this question to use SET @LV = (SELECT) gives an error, and returns 1, NULL. You definitely need to pay attention to which one you're using and what kind of result you want!

  • Sean Lange (3/31/2016)


    I like the question and the order by concern has already been pointed out. The part of the explanation that really bothers me is this: "It is recommended that you use SET @local_variable instead of SELECT @local_variable". I have never seen anywhere that officially recommends using SET instead of SELECT for setting variable values. It is important to understand the differences and nuances but they both their place.

    I don't think the statement recommending "SET" is even related to the question. If you need to a value from a table to @local_variable, you will have to use SELECT. The MSDN article is comparing "set @a = 1" to "select @a = 1." The question is really illustrating the "last one wins" when a multiple rows are returned, and that when no rows are returned, and local variable is not affected.

    Don Simpson



    I'm not sure about Heisenberg.

  • DonlSimpson (3/31/2016)


    Sean Lange (3/31/2016)


    I like the question and the order by concern has already been pointed out. The part of the explanation that really bothers me is this: "It is recommended that you use SET @local_variable instead of SELECT @local_variable". I have never seen anywhere that officially recommends using SET instead of SELECT for setting variable values. It is important to understand the differences and nuances but they both their place.

    I don't think the statement recommending "SET" is even related to the question. If you need to a value from a table to @local_variable, you will have to use SELECT. The MSDN article is comparing "set @a = 1" to "select @a = 1." The question is really illustrating the "last one wins" when a multiple rows are returned, and that when no rows are returned, and local variable is not affected.

    You can use SET to get a value from a table. SET @a = (SELECT TOP 1 blah FROM meh ORDER BY gah). And I would say you should default to using SET for exactly the reason that it will error out if you get more than one result.

  • Interesting question. I learned something new. Thanks!

  • Interesting question which remembered me a discussion in an old question in a MSDN/Technet forum.

  • As others have already said, there is no way to guarantee execution order, so there are three answers that are "sort of" correct (0,0 / 1,1 / 2,2 - the first query can return any of the three rows; the second will not change the value); and only one answer that is really correct (it depends).

    Someone comments on this topic that SELECT is more forgiving than SET. Which is exactly the reason why I prefer using SET. If I make a mistake in my code and get no rows or multiple rows, I much rather have an error than get what I consider to be a random value without any warning.

    Unfortunately, SET does not allow us to assign values from a single query to multiple variables. I am still waiting for the day Microsoft finally implements "SET (@var1, @var2, @var3) = SELECT (col1, col2, col3) FROM dbo.MyTable WHERE KeyVal = @MyKey;"


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Hugo Kornelis (4/3/2016)


    As others have already said, there is no way to guarantee execution order, so there are three answers that are "sort of" correct (0,0 / 1,1 / 2,2 - the first query can return any of the three rows; the second will not change the value); and only one answer that is really correct (it depends).

    Someone comments on this topic that SELECT is more forgiving than SET. Which is exactly the reason why I prefer using SET. If I make a mistake in my code and get no rows or multiple rows, I much rather have an error than get what I consider to be a random value without any warning.

    Unfortunately, SET does not allow us to assign values from a single query to multiple variables. I am still waiting for the day Microsoft finally implements "SET (@var1, @var2, @var3) = SELECT (col1, col2, col3) FROM dbo.MyTable WHERE KeyVal = @MyKey;"

    Have you created a request at Connect about this proposal ? If yes , post the corresponding link to have more approvals about it ( I hope you will be able to understand my poor English ).

  • Hi Patrick,

    Here's my original requests for row constructors. The request focuses on updates, and it has a comment by Itzik Ben-Gan that adds a lot of useful cases for row constructors in predicates, but full implementation of row contructurs would also allow the SET syntax.

    https://connect.microsoft.com/SQLServer/feedback/details/299231/add-support-for-ansi-standard-row-value-constructors.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Very interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • patricklambin (4/3/2016)


    Have you created a request at Connect about this proposal ? If yes , post the corresponding link to have more approvals about it ( I hope you will be able to understand my poor English ).

    Patrick, your English is better than you're giving yourself credit for. I never have trouble understanding you.

  • Even with the primary clustered key, are we sure here that the select always returns 2 at the last?

    Wonder why they always used to tell us there is no guaranteed order in a SELECT query unless explicitly specified by an ORDER BY..

    "The price of anything is the amount of life you exchange for it" - Henry David Thoreau
  • Mohit Dhiman (7/13/2016)


    Even with the primary clustered key, are we sure here that the select always returns 2 at the last?

    Wonder why they always used to tell us there is no guaranteed order in a SELECT query unless explicitly specified by an ORDER BY..

    Because without an ORDER BY there is not guarantee of the order. It will usually return them in the order of the clustered index....up to a point. And with no order by on a query with more then 1 table what would be the "default" order?

    Here is an excellent article about the default order of rows. https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/[/url]

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean Lange (7/13/2016)


    Mohit Dhiman (7/13/2016)


    Even with the primary clustered key, are we sure here that the select always returns 2 at the last?

    Wonder why they always used to tell us there is no guaranteed order in a SELECT query unless explicitly specified by an ORDER BY..

    Because without an ORDER BY there is not guarantee of the order. It will usually return them in the order of the clustered index....up to a point. And with no order by on a query with more then 1 table what would be the "default" order?

    Here is an excellent article about the default order of rows. https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/[/url]

    That's what I am saying and i believe has been already agreed enough on this thread : there's no guarantee that 2 would be returned at last each time we run the select without an ORDER BY. (although, with this small set most of the times it would)

    "The price of anything is the amount of life you exchange for it" - Henry David Thoreau

Viewing 15 posts - 16 through 30 (of 31 total)

You must be logged in to reply to this topic. Login to reply