Creating Nonclustered Indexes
You can create multiple nonclustered indexes on a table or indexed view. Generally, nonclustered indexes are created to improve the performance of frequently used queries not covered by the clustered index.
Typical Implementations
Nonclustered indexes are implemented in the following ways:
PRIMARY KEY and UNIQUE constraints
When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.
When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist. For more information, see PRIMARY KEY Constraints and UNIQUE Constraints.
Index independent of a constraint
By default, a nonclustered index is created if clustered is not specified. The maximum number of nonclustered indexes that can be created per table is 999. This includes any indexes created by PRIMARY KEY or UNIQUE constraints, but does not include XML indexes.
Nonclustered index on an indexed view
After a unique clustered index has been created on a view, nonclustered indexes can be created. For more information, see Creating Indexed Views.
Index with Included Columns
When you create a nonclustered index to cover a query, you can include nonkey columns in the index definition to cover the columns in the query that are not used as primary search columns. Performance gains are achieved because the query optimizer can locate all the required column data within the index; the table or clustered index is not accessed. For more information, see Index with Included Columns.
Index with a Filter Predicate
A filtered index is an optimized nonclustered index, especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance, reduce index maintenance costs, and reduce index storage costs compared with full-table indexes.
For more information, see Filtered Index Design Guidelines.
Disk Space Requirements
For information about disk space requirements for nonclustered indexes, see Determining Index Disk Space Requirements.
Performance Considerations
Although it is important that the index contain all columns used by the query, avoid adding columns unnecessarily. Adding too many index columns, either key or nonkey, can have the following performance ramifications:
Fewer index rows will fit on a page resulting in disk I/O increases and reduced cache efficiency.
More disk space will be required to store the index.
Index maintenance may increase the time that is required to perform modifications, inserts, updates, deletes, or merges to the underlying table or indexed view.
You should determine whether the gains in query performance outweigh the effect to performance during data modification and in additional disk space requirements. For more information about evaluating query performance, see Query Tuning.
Consider using a filtered index to improve performance when table data has columns with well defined subsets of rows, for example sparse columns, columns with mostly NULL values, columns with categories of values, and columns with distinct ranges of values. For more information, see Filtered Index Design Guidelines.
To create a PRIMARY KEY or UNIQUE constraint when you create a table
To create a PRIMARY KEY or UNIQUE constraint on an existing table
To create an index