Select vs Set Local Variable

Question

Post reply

Select vs Set Local Variable

Peter McLean

Old Hand

Points: 383
More actions
March 30, 2016 at 11:01 pm

#399952

Comments posted to this topic are about the item Select vs Set Local Variable

Viewing 15 posts - 1 through 15 (of 29 total)

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

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 1

Nice question.

But why 2 on this assignment?

SELECT @SelectVariable = ID

FROM @Table

Since they were inserted in as 0,2,1 the last value would be expected to be 1, like in a queue.

Does Primary Key creates an index which sorts them ascending?

Cheers,

Iulian

Carlo Romagnano SSC-Insane Points: 22750 More actions · Answer 2

Iulian -207023 (3/30/2016)
Nice question.
But why 2 on this assignment?
SELECT @SelectVariable = ID
FROM @Table
Since they were inserted in as 0,2,1 the last value would be expected to be 1, like in a queue.
Does Primary Key creates an index which sorts them ascending?
Cheers,
Iulian

That's true! Without an ORDER BY clause, there isn't no guarantee that the first select returns 2. Any value may be returned.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 3

Carlo Romagnano (3/31/2016)
Iulian -207023 (3/30/2016)
Nice question.
But why 2 on this assignment?
SELECT @SelectVariable = ID
FROM @Table
Since they were inserted in as 0,2,1 the last value would be expected to be 1, like in a queue.
Does Primary Key creates an index which sorts them ascending?
Cheers,
Iulian
That's true! Without an ORDER BY clause, there isn't no guarantee that the first select returns 2. Any value may be returned.

I wander if we can make it fail to return 2 if we run it 1000 times or any other way.

Toreador SSChampion Points: 11376 More actions · Answer 4

Carlo Romagnano (3/31/2016)
Without an ORDER BY clause, there isn't no guarantee that the first select returns 2. Any value may be returned.

This ^^^

It probably will always return 2 given the size of the table, and it's likely that this will remain true however many times you run it. But there's no guarantee, and the behaviour could easily change in a future release if the query engine changes.

tripleAxe SSCertifiable Points: 5707 More actions · Answer 5

Mmmm, interesting.

So it looks like the Primary Key constraint on the table means that the value 2 is returned last.

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 6

tripleAxe (3/31/2016)
Mmmm, interesting.
So it looks like the Primary Key constraint on the table means that the value 2 is returned last.

as per msdn: https://msdn.microsoft.com/en-us/library/ms189039.aspx

"You can define a primary key in SQL Server 2016 by using SQL Server Management Studio or Transact-SQL. Creating a primary key automatically creates a corresponding unique, clustered or nonclustered index."

But anyway indexes are for fast retrieval, they still do not guarantee the order records are returned.

tripleAxe SSCertifiable Points: 5707 More actions · Answer 7

Is this the same behaviour for table variables? Or are there any differences/gotchas to be aware of?

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 8

Stewart "Arturius" Campbell (3/31/2016)
Nice one, thanks Peter
Remember, if you do not specify that a PRIMARY KEY is NONCLUSTERED, it will be created as CLUSTERED (i.e. logically ordered). However, the order for a SELECT is still not guaranteed (things like e.g. parallel proccessing, etc could impact the "sort order" of the result set)

How could we make the query to use parallel processing?

Now obviously this is not applicable here since there are only 3 records;

but generally speaking I am not sure there is a way to tell query engine to run a query in parallel.

Rune Bivrin SSCrazy Eights Points: 8595 More actions · Answer 9

Stewart "Arturius" Campbell (3/31/2016)
The MAXDOP setting will let the enginre use parallel processing where the optimiser considers it neccessary, alternatively, use the MAXDOP query hint (but definitely not on a production server)

The MAXDOP hint only limits the Maximum Degree of Parallelism. It doesn't force parallelism in any way.

Just because you're right doesn't mean everybody else is wrong.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 10

Stewart "Arturius" Campbell (3/31/2016)
Nice one, thanks Peter
Remember, if you do not specify that a PRIMARY KEY is NONCLUSTERED, it will be created as CLUSTERED (i.e. logically ordered). However, the order for a SELECT is still not guaranteed (things like e.g. parallel proccessing, etc could impact the "sort order" of the result set)

The CI is likely being used to determine the order, but as Stweart and others have pointed out, without an ORDER BY clause, there's no guarantee.

Nice question. This is likely to generate some debate and discussion.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 11

It seems to be a trace flag that pushes parallel processing.

Check this out: forcing-a-parallel-query-execution-plan

I basically understand that:

We can make a query to get an execution plan that uses parallel processing.

Then we can make the query use that execution plan and see the results.

"There’s always a Trace Flag", and as long as there is a trace flag there is hope.

I tried to attach the OPTION (RECOMPILE, QUERYTRACEON 8649) to the query but no effect in the query plan here.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 12

That's why I prime my variables with null prior to doing that select. It helps to understand the possible data that could be returned.

Sean Lange SSC Guru Points: 286602 More actions · Answer 13

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 14

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.

Select vs Set Local Variable

Cookies on SQLServerCentral