tony.turner (5/13/2010)
GilaMonster (5/13/2010)
If, however there is an ORDER BY, and SQL can use an index to retrieve the data ordered, it avoids a sort operation.As I understand the section in BOL, this is no longer necessarily true
It is true. What that section is saying is that SQL will not necessarily return the data in a particular order if ORDER BY is not specified due to, among other things, the advanced scanning options available (the 'merry-go-round' scan.)
If you specify an ORDER BY statement and there's an index that SQL can use to retrieve the requested information already in that order then the query optimiser will pick that index and will put restrictions in place to ensure that no non-order preserving operations occur. It is an optimisation, sorts are expensive and if SQL can avoid doing a sort by taking advantage of the already sorted index, it will do so.
If there's no ORDER BY specified then the optimiser, knowing that it doesn't matter what order the rows are returned in, can use any operators or scan methods it has access to.
In fact, to quote from that section of BoL
This mechanism is also called "merry-go-round scanning" and demonstrates why the order of the results returned from a SELECT statement cannot be guaranteed without an ORDER BY clause.
If you note, the part of my post that you quoted saying is wrong referred to the case where there is an ORDER BY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability