March 30, 2016 at 11:01 pm
Comments posted to this topic are about the item Select vs Set Local Variable
March 30, 2016 at 11:29 pm
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
March 31, 2016 at 12:56 am
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.
March 31, 2016 at 1:33 am
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.
March 31, 2016 at 1:51 am
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.
March 31, 2016 at 2:35 am
Mmmm, interesting.
So it looks like the Primary Key constraint on the table means that the value 2 is returned last.
March 31, 2016 at 2:52 am
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.
March 31, 2016 at 3:19 am
This was removed by the editor as SPAM
March 31, 2016 at 3:24 am
Is this the same behaviour for table variables? Or are there any differences/gotchas to be aware of?
March 31, 2016 at 4:06 am
Stewart "Arturius" Campbell (3/31/2016)
Nice one, thanks PeterRemember, 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.
March 31, 2016 at 4:34 am
This was removed by the editor as SPAM
March 31, 2016 at 5:35 am
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.
March 31, 2016 at 6:05 am
Stewart "Arturius" Campbell (3/31/2016)
Nice one, thanks PeterRemember, 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.
March 31, 2016 at 6:08 am
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.
March 31, 2016 at 6:39 am
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.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply