Article ID: 820209 - View products that this article applies to.
Sometimes, the SQL Server optimizer uses a nonclustered index instead of a clustered index. This article documents when and why this behavior occurs.
In most situations, the behavior that is mentioned in "Summary" 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.
For more information about nonclustered indexes, visit the following Microsoft Web site:
For more information about clustered indexes, visit the following Microsoft Web site:
Contact us for more help
Connect with Answer Desk for expert help.