Index Architecture and Behavior - SQL Server Query Performance Tuning - page 138

You can run the same SELECT command again. Since retrieving a small number of rows through a nonclustered
index is more economical than a table scan, the optimizer used the nonclustered index on column c1 , as shown in
Figure 8-22 . The number of logical reads reported by STATISTICS IO is as follows:

Table 'Test1'. Scan count 1, logical reads 3
CPU time = 0 ms, elapsed time = 0 ms.

Figure 8-22. Execution plan with a nonclustered index

Even though retrieving a small result set using a column with high selectivity is a good pointer toward creating a
nonclustered index on the column, a clustered index on the same column can be equally beneficial or even better.
To evaluate how the clustered index can be more beneficial than the nonclustered index, create a clustered index on
the same column.

CREATE CLUSTERED INDEX icl ON dbo.Test1(C1);

Run the same SELECT command again. From the resultant execution plan (see Figure 8-22 ) of the preceding
SELECT statement, you can see that the optimizer used the clustered index (instead of the nonclustered index) even for
a small result set. The number of logical reads for the SELECT statement decreased from three to two (Figure 8-23 ).

Table 't1'. Scan count 1, logical reads 2
CPU time = 0 ms, elapsed time = 0 ms.

Figure 8-23. Execution plan with a clustered index

■ Because a table can have only one clustered index and that index is where the data is stored, I would generally
reserve the clustered index for the most frequently used access path to the data.

Note