Select vs Set Local Variable

Question

Select vs Set Local Variable

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Peter McLean Old Hand Points: 383 More actions · Answer 2

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.

Joe O'Connor SSC Eights! Points: 904 More actions · Answer 3

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!

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 4

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.

cdesmarais 49673 SSCommitted Points: 1863 More actions · Answer 5

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.

Aleksl-294755 Hall of Fame Points: 3514 More actions · Answer 6

Interesting question. I learned something new. Thanks!

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 7

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

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 8

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/

patricklambin SSCrazy Eights Points: 9994 More actions · Answer 9

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 ).

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 10

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/

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 11

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

Marcia J SSCertifiable Points: 5649 More actions · Answer 12

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.

Mohit Dhiman Say Hey Kid Points: 677 More actions · Answer 13

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 14

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]