Indexes Overview

An index is a database structure associated with a table or a view that speeds up the retrieval of rows by creating a lookup mechanism. Indexes improve the performance of queries that search for rows that match specific criteria. However, indexes also add overhead to data modification operations (INSERT, UPDATE, DELETE) because the index must also be updated.

Purpose of Indexes

• Faster Data Retrieval: Significantly speeds up SELECT statements by allowing the database engine to locate specific rows without scanning the entire table.
• Enforcing Uniqueness: Unique indexes ensure that no two rows in a table have the same index key value.
• Sorting and Grouping: Can help optimize ORDER BY and GROUP BY clauses.

Types of Indexes in SQL Server

Clustered Indexes

A clustered index determines the physical order of data in the table. Because of this, a table can have only one clustered index. The leaf nodes of a clustered index contain the data pages of the table. When a table has a clustered index, the table is called a clustered table.

• Characteristics:
• Stores the actual data rows in the leaf level, ordered by the index key.
• A table can have only one clustered index.
• The primary key constraint automatically creates a unique clustered index by default, but this can be changed.
• Excellent for range queries (e.g., `WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31'`).

Nonclustered Indexes

A nonclustered index contains the index key values and a pointer to the data row containing that key value. The leaf nodes of a nonclustered index contain pointers to the data rows. The order of the data rows in the table is not affected by nonclustered indexes.

• Characteristics:
• Stores index key values and pointers to the data rows.
• A table can have multiple nonclustered indexes.
• Leaf nodes point to either the clustered index key (if a clustered index exists) or the row locator (if the table is a heap).
• Useful for queries that search for specific values or small ranges.

Index Structures

Indexes are typically implemented as B-trees (Balanced Trees). A B-tree is a self-balancing tree data structure that keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time.

• Levels:
• Root Level: The top level of the tree.
• Intermediate Levels: One or more levels containing index pages that point to other index pages or to the leaf level.
• Leaf Level: The lowest level of the index. For clustered indexes, this contains the data pages. For nonclustered indexes, this contains the index keys and pointers to the data rows.

Key Concepts

• Index Key: The column(s) that are included in the index definition.
• Covering Index: A nonclustered index that includes all the columns required by a query, either as key columns or included columns. This allows the query to be satisfied directly from the index without having to access the base table.
• Heap: A table without a clustered index. Data is stored in no particular order.
• Fill Factor: A percentage that determines how full SQL Server makes the leaf level of an index when it's created or rebuilt. A lower fill factor leaves more empty space, which can be beneficial for tables with frequent updates and inserts, as it reduces page splits.

Common Index Types in SQL Server

• Unique Indexes: Enforce uniqueness on one or more columns.
• Clustered Indexes: Define the physical storage order of data.
• Nonclustered Indexes: Provide a logical ordering and pointers to data.
• Filtered Indexes: Nonclustered indexes that index a subset of rows in a table, defined by a WHERE clause. They can improve query performance and reduce index maintenance overhead.
• Columnstore Indexes: Designed for data warehousing and analytical workloads, offering significant compression and query performance benefits for large fact tables.

Managing Indexes

Indexes need to be maintained to ensure optimal performance. This includes rebuilding or reorganizing indexes that have become fragmented.

• Rebuilding: Recreates the index, often resulting in better structure and less fragmentation.
• Reorganizing: Defragments the index pages by moving data to fill empty space.

For more detailed information, refer to the Create Indexes and Reorganize and Rebuild Indexes documentation.