Nonclustered Indexes

A nonclustered index is one in which the logical ordering of a table is specified. The physical order of the rows is not the same as their indexed order. The leaf level of a nonclustered index contains pointers to rows on data pages.

You can have multiple nonclustered index per table. Consider using a nonclustered index for:

• Columns that contain a large number of unique values.

  For example, a part_id column of a parts table.

• Queries that return small or single-row results sets.

  For example:

  SELECT * FROM employee
  WHERE emp_id = 'PCM98509F'

• Queries that use an ORDER BY clause and the FASTFIRSTROW optimizer hint.

The leaf-level pages of a nonclustered index are index pages, which contain indexed column data and pointers to data pages. These data pages are unordered. Unless all columns in the SELECT list and all columns in the WHERE clause are part of the nonclustered index, SQL Server must first read the index (requiring one or more logical page I/Os depending on the depth of the index) and then read the data page pointed to by the index.

When these extra logical data page I/Os are required, the total number of page I/Os required to execute the query can increase dramatically for large results sets. In this case, a table scan is often more efficient than a nonclustered index. When the selectivity of a query reaches around 15 percent of the table size of a smaller table, it is usually less expensive for the optimizer to use a table scan than to use the nonclustered index. The SQL Server optimizer estimates how many physical I/Os would be required under each strategy, and chooses the optimal one.