patrickmcginnis59 10839 (5/27/2013)
I was under the impression that the table was either sorted (ordered by a clustered key) or not, so this is a new one for me!
Technically tables are unsorted sets regardless of indexes, however that's besides the point here.
When the optimiser comes up with a plan, it will know if an intermediate resultset is ordered and if so by what, however that's an all or nothing, it's either sorted or it's not sorted. Sorted resultsets can be from doing an ordered (range) scan of an index (clustered or nonclustered) or from a sort operation, other operators in the query are flagged as order preserving (things like nested loop joins are) or non-order preserving (hash joins). A sorted resultset allows for merge joins or stream aggregate operators to be used without needing a sort operation before them and possibly allows for a sorted output (per the order by) without needing a sort operator.
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)SQL In The Wild
: Discussions on DB performance with occasional diversions into recoverability
We walk in the dark places no others will enter
We stand on the bridge and no one may pass