Post reply

Select vs Set Local Variable

  • July 14, 2016 at 7:28 am

    #1890099

    Mohit Dhiman (7/14/2016)

    Sean Lange (7/13/2016)

    Mohit Dhiman (7/13/2016)

    Even with the primary clustered key, are we sure here that the select always returns 2 at the last?

    Wonder why they always used to tell us there is no guaranteed order in a SELECT query unless explicitly specified by an ORDER BY..

    Because without an ORDER BY there is not guarantee of the order. It will usually return them in the order of the clustered index....up to a point. And with no order by on a query with more then 1 table what would be the "default" order?

    Here is an excellent article about the default order of rows. https://blogs.msdn.microsoft.com/conor_cunningham_msft/2008/08/27/no-seatbelt-expecting-order-without-order-by/[/url]

    That's what I am saying and i believe has been already agreed enough on this thread : there's no guarantee that 2 would be returned at last each time we run the select without an ORDER BY. (although, with this small set most of the times it would)

    With only 3 rows I don't think you could get it to return anything else no matter how many times you run the query. With the way the query engine works today it will return those 3 rows in the order of the clustered index. I would of course not recommend counting on that in a production situation.

    _______________________________________________________________

    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/

Viewing post 31 (of 30 total)

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