SQL Server Documentation

Nonclustered Indexes

This section provides a comprehensive guide to understanding and implementing nonclustered indexes in SQL Server. Nonclustered indexes are a crucial component for optimizing query performance in relational databases.

What is a Nonclustered Index?

A nonclustered index is a data structure that contains the index key values from a table or view, and it has pointers to the actual data rows. Each row in the index table contains the key values and a row locator. The row locator is a pointer to the data row that contains the matching values. The data rows themselves are stored in a heap or in a clustered index.

Key characteristics of nonclustered indexes:

• Separate Structure: The index is a separate entity from the data pages.
• Order: The leaf level of a nonclustered index is sorted according to the index key.
• Row Locators: Each entry in the leaf level points to a data row. This can be a Row ID (RID) for heaps or the clustered index key for tables with a clustered index.
• Multiple Indexes: A table can have multiple nonclustered indexes, allowing for optimization of various query patterns.

How Nonclustered Indexes Improve Performance

Nonclustered indexes are most effective when they can satisfy a query completely without having to access the base table (a "covering index"), or when they significantly reduce the number of rows that need to be scanned.

• Faster Data Retrieval: By providing a sorted list of indexed columns, SQL Server can quickly locate the relevant rows without performing a full table scan.
• Index Seek: When a query can use an index to directly locate rows, it performs an "index seek," which is much faster than a "table scan."
• Covering Indexes: If all the columns required by a query are included in the nonclustered index (either as key columns or included columns), SQL Server can retrieve all necessary data directly from the index, eliminating the need to access the base table at all.

Creating Nonclustered Indexes

You can create a nonclustered index using the CREATE NONCLUSTERED INDEX statement.

CREATE NONCLUSTERED INDEX IX_Customer_LastName
ON Sales.Customer (LastName, FirstName)
INCLUDE (MiddleName);
                

In this example:

• IX_Customer_LastName is the name of the index.
• Sales.Customer is the table the index is created on.
• LastName and FirstName are the key columns, defining the sort order of the index.
• MiddleName is an included column. Data from included columns is stored at the leaf level of the index but is not part of the index key. This is useful for creating covering indexes.

Considerations for Using Nonclustered Indexes

• Index Maintenance Overhead: Every data modification (INSERT, UPDATE, DELETE) on the table requires the corresponding indexes to be updated, which incurs overhead. Too many indexes can slow down data modification operations.
• Disk Space: Indexes consume disk space.
• Query Patterns: Design indexes based on your most frequent and performance-critical query patterns.
• Column Order: The order of columns in a composite nonclustered index is important. Place columns with higher selectivity (fewer duplicate values) earlier in the index definition.

When to Use Nonclustered Indexes

• For columns frequently used in the WHERE clause of queries.
• For columns used in JOIN conditions.
• To create covering indexes for specific queries.
• When you need to enforce uniqueness on a set of columns that are not part of the clustered index.