Sql-server – why does the clustered index scan have ordered = false

sql serversql-server-2008

When I look to at my query plan, I see plenty of clustered index seeks (yay!) and scans (not quite so yay!) but in some of them ordered = False. I will mention that we have parallelism involved, though it does not appear to have any relation to these. The indexes were set up with ordering.

What does this mean? That the query plan is ignoring this order? Or that the query is choosing not to order? Or… heck, I have no idea what I'm looking at…!

Best Answer

It just means that the query optimiser doesn't require an explicit order guarantee either for some later operator in the plan (e.g. merge join or stream aggregate) or to avoid a sort because you have explicitly requested an order by.

When ordered = false you might in some circumstances get an allocation ordered scan rather than a scan that follows the linked list of the leaf pages in index key order for example.

Related Solutions

Sql-server – Why the query scan all the partitions

Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.

Sql-server – parallelism repartitions, ordering, and hash matches

Hash matches are not necessarily bad @ all. Please see Grant Fritchey's awesome (and free!) book: SQL Server Execution Plans

Best Answer

Related Solutions

Sql-server – Why the query scan all the partitions

Sql-server – parallelism repartitions, ordering, and hash matches

Related Question