This document provides guidelines and best practices for designing nonclustered indexes in SQL Server. Effective index design is crucial for query performance.

When to Use Nonclustered Indexes

Nonclustered indexes are beneficial for the following scenarios:

• Queries that frequently filter data on columns not included in the clustered index.
• Queries that frequently join tables on columns not covered by the clustered index.
• Queries that require sorting or grouping data on columns not in the clustered index.
• When you need to cover a query entirely by including all columns required by the SELECT list and WHERE clause (covering index).

Key Design Considerations

1. Selectivity

Choose columns that are highly selective for index keys. High selectivity means that a column has many distinct values, so the index can quickly narrow down the search results. Low selectivity (e.g., a boolean flag) might not benefit significantly from being an index key.

2. Column Order

The order of columns in a composite nonclustered index is important. Place columns that are most frequently used in the WHERE clause first. For range queries, the column used for the range should generally be placed last.

Consider the following example:

-- Query:
SELECT CustomerName, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-03-31'
AND CustomerID = 123;

-- Good Index:
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_OrderDate
ON Orders (CustomerID ASC, OrderDate ASC);
        

In this case, placing CustomerID first is efficient because it's used for an equality predicate.

3. Including Columns (INCLUDE Clause)

Use the INCLUDE clause to add columns that are referenced in the SELECT list but are not part of the index key. This can create a covering index, which allows the query to be satisfied entirely from the index without having to access the base table.

-- Query:
SELECT OrderID, TotalAmount
FROM Orders
WHERE CustomerID = 456;

-- Covering Index:
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID_Covering
ON Orders (CustomerID ASC)
INCLUDE (OrderID, TotalAmount);
        

This index allows the query to be resolved solely from the index.

4. Column Data Types

Shorter data types are generally better for index keys, as they consume less space and can be processed faster.

• Prefer VARCHAR over NVARCHAR if Unicode is not required.
• Use appropriate integer types (e.g., INT, BIGINT) rather than large DECIMAL or FLOAT types for keys if possible.

5. Avoid Excessive Indexing

Each index adds overhead to data modification operations (INSERT, UPDATE, DELETE) because the index must also be updated. Avoid creating indexes that are rarely used.

6. Index Maintenance

Regularly maintain your indexes by rebuilding or reorganizing them to combat fragmentation and improve performance. Monitor index fragmentation levels using system catalog views.

Types of Nonclustered Indexes

• Unique Nonclustered Indexes: Enforce uniqueness on a set of columns.
• Non-Unique Nonclustered Indexes: Allow duplicate values.
• Filtered Indexes: Indexes defined on a subset of rows in a table, useful for queries that consistently access a specific subset of data.

Index Overhead

Consider the trade-offs:

• Storage: Indexes consume disk space.
• Write Performance: INSERT, UPDATE, and DELETE operations become slower as the number and complexity of indexes increase.
• Read Performance: Well-designed indexes significantly improve query performance.

Further Reading

• Clustered vs. Nonclustered Indexes
• Filtered Indexes
• Index Maintenance