Microsoft KB Archive/820209

= Why the SQL Server Optimizer Uses a Nonclustered Index Instead of a Clustered Index =

Article ID: 820209

Article Last Modified on 5/16/2007

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



SUMMARY
Sometimes, the SQL Server optimizer uses a nonclustered index instead of a clustered index. This article documents when and why this behavior occurs.



MORE INFORMATION
In most situations, the behavior that is mentioned in &quot;Summary&quot; section is based on the number of read operations that must be performed. When many read operations must be performed, the SQL Server optimizer must scan most of the rows in the table. The SQL Server optimizer can read the clustered key values from nonclustered index pages more quickly than it can read the key values from clustered index pages.

The clustered index page contains the clustered index key values and the rest of the row's data. The nonclustered index page contains the nonclustered index key values and the additional columns where the index is created. As a result, one nonclustered index page may contain more clustered key index values per page than the clustered index page. Therefore, it is faster for the SQL Server optimizer to read from the nonclustered index pages to reduce the input/output operations that are necessary to perform read operations.

