Select vs Set Local Variable

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

  • 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

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

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

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

  • Mmmm, interesting.

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

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

  • This was removed by the editor as SPAM

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

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

  • This was removed by the editor as SPAM

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

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

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

  • 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