Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / DB Engine / SQL Server / September 2007

Tip: Looking for answers? Try searching our database.

Ordered = False for Clustered Index Scan

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Hassan - 30 Sep 2007 06:52 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.