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
March 31, 2016 at 3:19 am

This was removed by the editor as SPAM
March 31, 2016 at 4:34 am

This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 30 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: 22596 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: 11358 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: 5706 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: 5706 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: 8436 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: 287024 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.