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

Article translations Article translations
Article ID: 820209 - View products that this article applies to.
Expand all | Collapse all

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 "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.

REFERENCES

For more information about nonclustered indexes, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/aa174537(SQL.80).aspx

For more information about clustered indexes, visit the following Microsoft Web site:
http://msdn2.microsoft.com/en-us/library/aa174523(SQL.80).aspx

Properties

Article ID: 820209 - Last Review: May 16, 2007 - Revision: 5.4
APPLIES TO
  • Microsoft SQL Server 2000 Standard Edition
Keywords: 
kbtshoot kbinfo KB820209

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com