When i look at the properties of the Clustered Index Scan Operator in
execution plan, it states that ordered = False..What does that mean ? I
thought a clustered index is always ordered.
On a side note, where can i learn more about the other properties and also
the different operators one sees in the execution plan ?
Thanks
Tibor Karaszi - 30 Sep 2007 08:24 GMT
All indexes are ordered. A cluster index is the data meaning that the data is ordered according to
the clustered index. However, "ordered" doesn't mean it is ordered physically. Pages splits etc lead
to fragmentation meaning that SQL Server will "jump around" in the data file while following the
linked list (order of the index). Because of this, it can be more beneficial to use the IAM page to
scan the file (relevant pages) physically instead of "jump around" in the database file. There are
good sections in Books Online, for instance the "Physical Database Architecture".

Signature
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
> When i look at the properties of the Clustered Index Scan Operator in execution plan, it states
> that ordered = False..What does that mean ? I thought a clustered index is always ordered.
[quoted text clipped - 3 lines]
>
> Thanks
Dan Guzman - 30 Sep 2007 13:53 GMT
To add on to Tibor's response, Ordered=True tells the storage engine that
data must be returned in key sequence. The ordered requirement is needed by
some execution plan operators (e.g. merge join) or to address ordering of
the result set (ORDER BY).
Ordered=False indicates that there is no requirement that data be returned
in a particular order so the storage engine might use scan techniques that
do not guarantee ordering, such as IAM or advanced scanning.

Signature
Hope this helps.
Dan Guzman
SQL Server MVP
> When i look at the properties of the Clustered Index Scan Operator in
> execution plan, it states that ordered = False..What does that mean ? I
[quoted text clipped - 4 lines]
>
> Thanks