Microsoft Learn

SQL 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 (most commonly a B-tree or hash table) that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure.

Types of SQL Indexes

Databases typically support several types of indexes, each with its own strengths and use cases.

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. The clustered index key is used to locate the leaf nodes of the clustered index.

Key Characteristics:

• Defines the physical storage order of data.
• Only one per table.
• Leaf nodes contain the actual data rows.

Example of creating a clustered index:

CREATE CLUSTERED INDEX IX_CustomerID
ON Customers (CustomerID);

Nonclustered Indexes

A nonclustered index is a data structure separate from the data rows that contains the nonclustered index key values and a row locator for each key value. The row locator indicates where to find the associated data row. A nonclustered index is sorted on the index key values; the data rows are stored in a separate structure, typically a clustered index or heap.

Key Characteristics:

• Does not dictate the physical storage order of data.
• Can have multiple per table.
• Leaf nodes contain index keys and pointers to data rows.

Example of creating a nonclustered index:

CREATE NONCLUSTERED INDEX IX_LastName
ON Customers (LastName);

Unique Indexes

A unique index ensures that all index keys are unique. This means that if you try to insert or update a row that has a duplicate key value, the database will return an error. Unique indexes are often used to enforce data integrity.

Key Characteristics:

• Enforces uniqueness of indexed column(s).
• Can be clustered or nonclustered.

CREATE UNIQUE NONCLUSTERED INDEX UQ_Email
ON Users (Email);

Full-Text Indexes

Full-text indexes are specialized indexes that allow you to perform complex linguistic searches on character string data. They are designed for searching through large amounts of text efficiently.

Key Characteristics:

• Optimized for text searches.
• Supports linguistic analysis (stemming, thesaurus).

When to Use Indexes

Indexes are most effective for columns that are frequently used in:

• WHERE clauses
• JOIN conditions
• ORDER BY clauses
• GROUP BY clauses

Index Maintenance

Regular maintenance of indexes, such as rebuilding or reorganizing them, can help maintain optimal performance. This is especially important after a large number of data modifications.

For more in-depth information on specific database systems (e.g., SQL Server, PostgreSQL, MySQL), please refer to their respective documentation.