Indexes (SQL Server Database Engine)

Last updated: October 26, 2023 Contributors: Microsoft Docs

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations on a table. With an index, you can locate rows of data more quickly and efficiently than by scanning every row in the table. When you create an index on one or more columns of a table, the database engine also creates a structure that uses the values in those columns to store the rows in a sorted order. This structure allows the database engine to quickly find rows based on the values in the indexed columns.

Types of Indexes

Clustered Indexes

A clustered index determines the physical order of data in the table. Because rows can only be stored in one order, a table can have only one clustered index. A clustered index key is used to uniquely identify each row in the table. When a clustered index is created, the data rows themselves are stored in the leaf nodes of the clustered index B-tree structure, ordered by the clustered index key. This means that when you query data using the clustered index key, the database engine can directly locate the data rows.

Nonclustered Indexes

A nonclustered index is a separate data structure from the data rows. The leaf nodes of a nonclustered index contain pointers to the actual data rows. Because a nonclustered index is separate from the data rows, you can create multiple nonclustered indexes on a single table. Each nonclustered index is sorted on its own index key, and the leaf nodes contain row locators that point to the data rows in the table or to the clustered index key.

Unique Indexes

A unique index enforces the uniqueness of the values in the indexed columns. If you try to insert or update a row with a value that already exists in the unique index, the database engine will raise an error. This is often used to enforce data integrity, such as ensuring that no duplicate primary keys exist.

Filtered Indexes

A filtered index is an optimized nonclustered index on a subset of rows in a table. A filtered index is created by specifying a WHERE clause in the CREATE INDEX statement. This allows you to index specific rows that are frequently queried, reducing the size of the index and improving query performance. For example, you might create a filtered index on active orders or pending transactions.

Columnstore Indexes

Columnstore indexes store data column by column, rather than row by row. This is highly efficient for analytical workloads that involve scanning large amounts of data, such as data warehousing. They can significantly improve query performance for aggregations and scans by reducing the I/O required.

Benefits of Using Indexes

Faster Data Retrieval: Indexes significantly speed up SELECT queries by allowing the database engine to quickly locate specific rows.
Improved Query Performance: Well-designed indexes can reduce the need for table scans, leading to faster query execution times.
Data Integrity: Unique indexes help enforce data integrity by preventing duplicate values in indexed columns.
Efficient Sorting and Grouping: Indexes can help optimize ORDER BY and GROUP BY clauses.

Considerations

While indexes are beneficial, they also have considerations:

Storage Space: Indexes require additional disk space to store their structures.
Performance Overhead: Indexes add overhead to INSERT, UPDATE, and DELETE operations because the index structures must also be updated.
Maintenance: Indexes may need to be rebuilt or reorganized periodically to maintain optimal performance.

Creating and Managing Indexes

You can create indexes using the CREATE INDEX statement in SQL Server. For example:

CREATE CLUSTERED INDEX IX_Customers_LastName
ON Customers (LastName);

CREATE NONCLUSTERED INDEX IX_Orders_OrderDate
ON Orders (OrderDate);

You can also use SQL Server Management Studio (SSMS) to visually create and manage indexes.