MSDN Documentation

Microsoft Developer Network

Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. An index is a data structure (typically a B-tree) that stores a copy of a subset of a table's columns or entire rows, sorted according to a specific order. This allows the database engine to quickly locate rows without scanning the entire table.

Types of Indexes

SQL Server supports several types of indexes to optimize performance based on different query patterns:

  • Clustered Indexes: Define the physical order of data in the table. A table can have only one clustered index. The leaf level of a clustered index is the data rows themselves.
  • Nonclustered Indexes: Have a logical order that is different from the physical order of the data. The leaf level of a nonclustered index contains pointers to the data rows in the table. A table can have multiple nonclustered indexes.
  • Unique Indexes: Enforce the uniqueness of values in one or more columns.
  • Columnstore Indexes: Used for data warehousing and large analytical workloads, these indexes store data in a columnar format for improved compression and query performance.
  • Filtered Indexes: Nonclustered indexes that include only a subset of rows in a table, specified by a WHERE clause.
  • XML Indexes: Optimize queries that search within XML data stored in XML data type columns.
  • Spatial Indexes: Optimize queries that involve spatial data types.
  • Full-Text Indexes: Support full-text searching capabilities on character-based data.

Creating and Managing Indexes

Indexes can be created using the CREATE INDEX statement. It is crucial to choose the right columns to index based on query patterns and to maintain them regularly.

Syntax Example: Creating a Nonclustered Index


CREATE NONCLUSTERED INDEX IX_CustomerName
ON Sales.Customer (LastName, FirstName);

Syntax Example: Creating a Clustered Index


CREATE CLUSTERED INDEX PK_ProductID
ON Production.Product (ProductID);

Important Considerations:

  • Indexes can improve read performance but can also slow down data modification operations (INSERT, UPDATE, DELETE) because the index needs to be updated as well.
  • Choose indexed columns wisely, typically those used frequently in WHERE clauses, JOIN conditions, and ORDER BY clauses.
  • Avoid indexing columns with very low cardinality (few unique values) unless they are part of a composite index.
  • Monitor index fragmentation and rebuild or reorganize indexes as needed.

Index Maintenance

Over time, indexes can become fragmented due to data modifications, leading to performance degradation. SQL Server provides commands to manage index fragmentation:

  • ALTER INDEX ... REBUILD: Reorganizes the index data and structures.
  • ALTER INDEX ... REORGANIZE: Defragments the index by merging pages and moving pages to be contiguous.
"The right index can make the difference between a query that runs in milliseconds and one that takes hours."

Further Reading