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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)

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://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_75mb.asp


For more information about clustered indexes, visit the following Microsoft Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1tbn.asp

Modification Type:MajorLast Reviewed:4/6/2004
Keywords:kbtshoot kbinfo KB820209 kbAudDeveloper
©2004 Microsoft Corporation. All rights reserved.