The Execution Engine - Inside the SQL Server Query Optimizer - page 33

Figure 2-3: Properties of the Clustered Index Scan operator.
Notice that the Ordered property shows True . If you run the same query without
the ORDER BY clause, the Ordered property will, unsurprisingly, show False . In some
other cases, SQL Server can benefit from reading the table in the order specified by the
clustered index. One example is shown later in this chapter in Figure 2-15, where a Stream
Aggregate operator can benefit from the fact that a Clustered Index Scan operator can
easily obtain the data already sorted.
Next, I will show you an example of an Index Scan operator. This example uses a
non-clustered index to cover a query; that is, it can solve the entire query without
accessing the base table (bearing in mind that a non-clustered index usually contains only