Good indexes are crucial for optimal performance in SQL Server. To create effective indexes, it is important to understand what indexes are and how SQL Server utilizes them to evaluate queries. In this article, we will explore the basics of indexes, the types of indexes in SQL Server, and how they are used.

What is an Index?

An index is a structure within SQL Server that allows for quick retrieval of specific rows within a table. Think of an index as the back of a textbook, where you can quickly find specific information. Similarly, an index in SQL Server is defined on one or more columns, known as key columns. These key columns are the values that the index will be used to search for. Just like a book index, the index is sorted by the key columns.

If an index is created with more than one key column, it is called a composite index. The general structure of an index is that of a balanced tree, also known as a B-tree. The index consists of a single root page, zero or more intermediate levels, and a leaf level. Each page is an 8 kilobyte chunk of the data file, identified by a combination of File ID and Page number.

Clustered and Nonclustered Indexes

In SQL Server, there are two main types of indexes: clustered indexes and nonclustered indexes. A clustered index defines the logical order of the table and the leaf level of the clustered index contains the actual data pages of the table. Only one clustered index can be created per table. If a table does not have a clustered index, it is referred to as a heap.

On the other hand, nonclustered indexes are separate from the table. The leaf level of a nonclustered index contains a pointer to either the clustered index key or the Row Identifier (RID) for each index row. The RID is a unique identifier for a row in a heap. Each row of a nonclustered index has a reference to the complete data row.

Index Limits

There are certain limitations on indexes in SQL Server. The size of an index key is limited to a maximum of 900 bytes and a maximum of 16 columns. Larger index keys result in more pages in the index and a deeper index tree, which can decrease efficiency and use more storage space.

In SQL Server 2005 and earlier versions, there was a limitation of 250 indexes per table (one clustered and 249 non-clustered). In SQL Server 2008 and later versions, this limitation was increased to 1000 indexes (one clustered and 999 non-clustered) with the addition of filtered indexes. However, it is important to note that having too many indexes can increase the size of the table and impact maintenance operations.

How SQL Server Uses Indexes

If a table does not have an index, SQL Server needs to read the entire table to find all occurrences of a value. However, if a table has an index, it speeds up the process of locating values in two ways:

1. The index is sorted in the order of the key columns, allowing SQL Server to ignore the remaining portion of the table once all matching values have been found.
2. The tree structure of the index enables a divide-and-conquer approach to locating rows, quickly excluding large portions of the table from the search.

SQL Server can perform four basic operations on an index: scan, seek, lookup, and update. An index scan involves reading all leaf pages of the index, while an index seek uses the B-tree structure to locate specific values or the beginning of a range of values. Lookups occur when an index does not contain all the columns required for a query, and SQL Server needs to fetch the remaining columns from the clustered index or heap. Updates involve making changes to both the base table and any indexes that contain the affected columns.

Considerations for Creating Indexes

When creating indexes, there are several considerations to keep in mind:

• Clustered indexes should be narrow, as the clustering key is part of all nonclustered indexes.
• Composite nonclustered indexes are generally more useful than single column indexes, unless queries filter on one column at a time.
• Indexes should not be wider than necessary to avoid wasting space and increasing the overhead of data modifications.
• If an index is unique, specify it as such to help the optimizer generate more optimal execution plans.
• Be cautious when creating numerous indexes on frequently modified tables, as it can slow down data modifications.

In the next parts of this series, we will delve deeper into clustered indexes and nonclustered indexes, exploring their differences and considerations for creating them.

Be sure to read all parts of this series:

1. The Basics of Indexes
2. The Clustered Index
3. The Nonclustered Index