Select vs Set Local Variable

  • Peter McLean

    Old Hand

    Points: 383

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

  • Iulian -207023

    SSCertifiable

    Points: 7509

    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: 21984

    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: 7509

    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: 11257

    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: 5605

    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: 7509

    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

  • tripleAxe

    SSCertifiable

    Points: 5605

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

  • Iulian -207023

    SSCertifiable

    Points: 7509

    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

  • Rune Bivrin

    SSCertifiable

    Points: 7824

    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: 286982

    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.

  • Iulian -207023

    SSCertifiable

    Points: 7509

    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

    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 31 total)

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