Nonclustered Index Design Guidelines
A nonclustered index contains the index key values and row locators that point to the storage location of the table data. For more information about nonclustered index architecture, see Nonclustered Index Structures.
You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes should be designed to improve the performance of frequently used queries that are not covered by the clustered index.
Similar to the way you use an index in a book, the query optimizer searches for a data value by searching the nonclustered index to find the location of the data value in the table and then retrieves the data directly from that location. This makes nonclustered indexes the optimal choice for exact match queries because the index contains entries describing the exact location in the table of the data values being searched for in the queries. For example, to query the HumanResources.Employee table for all employees that report to a specific manager, the query optimizer might use the nonclustered index IX_Employee_ManagerID; this has ManagerID as its key column. The query optimizer can quickly find all entries in the index that match the specified ManagerID. Each index entry points to the exact page and row in the table, or clustered index, in which the corresponding data can be found. After the query optimizer finds all entries in the index, it can go directly to the exact page and row to retrieve the data.
Database Considerations
Consider the characteristics of the database when designing nonclustered indexes.
Databases or tables with low update requirements, but large volumes of data can benefit from many nonclustered indexes to improve query performance. Consider creating filtered indexes for well-defined subsets of data to improve query performance, reduce index storage costs, and reduce index maintenance costs compared with full-table nonclustered indexes.
Decision Support System applications and databases that contain primarily read-only data can benefit from many nonclustered indexes. The query optimizer has more indexes to choose from to determine the fastest access method, and the low update characteristics of the database mean index maintenance will not impede performance.
Online Transaction Processing applications and databases that contain heavily updated tables should avoid over-indexing. Additionally, indexes should be narrow, that is, with as few columns as possible.
Large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes.
Query Considerations
Before you create nonclustered indexes, you should understand how your data will be accessed. Consider using a nonclustered index for queries that have the following attributes:
Use JOIN or GROUP BY clauses.
Create multiple nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
Queries that do not return large result sets.
Create filtered indexes to cover queries that return a well-defined subset of rows from a large table.
Contain columns frequently involved in search conditions of a query, such as WHERE clause, that return exact matches.
Column Considerations
Consider columns that have one or more of these attributes:
Cover the query.
Performance gains are achieved when the index contains all columns in the query. The query optimizer can locate all the column values within the index; table or clustered index data is not accessed resulting in fewer disk I/O operations. Use index with included columns to add covering columns instead of creating a wide index key. For more information, see Index with Included Columns.
If the table has a clustered index, the column or columns defined in the clustered index are automatically appended to the end of each nonclustered index on the table. This can produce a covered query without specifying the clustered index columns in the definition of the nonclustered index. For example, if a table has a clustered index on column C, a nonclustered index on columns B and A will have as its key values columns B, A, and C.
Lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.
If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is generally more efficient. For this type of data, consider creating a filtered index on a distinct value that only occurs in a small number of rows. For example, if most of the values are 0, the query optimizer might use a filtered index for the data rows that contain 1.
Index Options
There are several index options that can be specified when you create a nonclustered index. You should give special consideration to the following options:
FILLFACTOR
ONLINE
For more information, see Setting Index Options.