Databases Reference
In-Depth Information
Both the Table Scan and Clustered Index Scan operations are similar in that they
scan the entire base table, but the former is used for heaps and the second one for
clustered indexes.
Sorting is something to consider when it comes to scans, because even when the data in
a clustered index is stored sorted, using a Clustered Index Scan does not guarantee that
the
results
will be sorted unless this is explicitly requested. By not automatically sorting
the results, the Storage Engine has the option to find the most efficient way to access this
data without worrying about returning it in an ordered set. Examples of these efficient
methods include an advanced scanning mechanism called "merry-go-round scanning,"
which allows multiple query executions to share full table scans so that each execution
may join the scan at a different location. Alternatively, the Storage Engine may also use an
allocation order scan, based on Index Allocation Map (IAM) pages, to scan the table. I'm
not going to go into more detail regarding these techniques, because what's important
right now is that they exist, and the Storage Engine has the option of implementing them.
If you want to know whether your data has been sorted, the
Ordered
property can show
if the data was returned in a manner ordered by the Clustered Index Scan operator. So,
for example, the clustering key of the
Person.Address
table is
AddressID
, and if you
run the following query and look at the tooltip of the Clustered Index Scan operator, you
will get something similar to what is shown in Figure 2-3.
SELECT
*
FROM
Person
.
Address
ORDER
BY
AddressID
Listing 2-3.
