See Also: Main_Page - Database Administration - Database Design - Indexing

Indexes are data structures that help SQL Server find particular rows of data requested by each query. When chosen and maintained appropriately, indexes can make dramatic improvements in applications' performance.

A SQL Server index can be compared to an index in a book. If you have an index, you can quickly find the topic you're looking for. If you don't have an index, you might have to read the entire book to find specific information.

Index Architecture

SQL Server supports two main types of indexes: clustered and non-clustered. Both types are implemented using a b-tree structure. At the top of each index is the root node, which contains index rows. These store pointers to the next level of index tree - the intermediate nodes. Each index can have multiple intermediate nodes, which in turn contain pointers to the next level of the index until we reach the lowest level, known as the leaf level.

Clustered Indexes

The clustered index determines the logical order in which table data is stored because the leaf level of the clustered index consists of the actual data pages of the table. For instance, if you build a clustered index on the last_name column then data will be stored based on values in the last_name column. If you create a clustered index on the combination of facility_id and employee_id then data will be ordered based on values of facility_id and employee_id.

Since you can only create one clustered index per table you should consider choosing the columns on which this index will be created carefully. There are several considerations for chosing a clustering key. A clustered index key should be narrow, as it serves as the row's identifier and is present in all nonclustered indexes. It should be unique because if a clustered index is not unique SQL will make it unique by adding a hidden 'uniqifier' column to rows with duplicate values. A clustered index's key columns should not change because when the clustered index key values change for a row, that row must be physically moved. Lastly a clustered index key where the values inserted are always larger than existing values will minimise fragmentation.

Nonclustered Indexes

Nonclustered indexes are stored separate from the table. The leaf level of the nonclustered index contains the index keys along with a 'pointer' to the row that the index referes to. If the underlying table has a clustered index, that pointer is the clustered index key. If the underlying table does not have a clustered index, the pointer is the RID (Row Identifier), an 8 byte combination of File ID, Page Number and slot index.

In SQL Server 2005 and earlier, a maximum of 249 nonclustered indexes could be created on a table. In SQL Server 2008 that limit has been increased and now 999 nonclustered indexes can be created on a single table.

Nonclustered indexes should be considered for columns or combinations of columns that are frequently used in joins or are frequently used in the where clause of queries.

Covering Indexes

A covering index is one that contains all of the columns needed for a specific query. Hence an index may be covering for one query but not for another. If an index covers a query then SQL can evaluate the query without needing to go to the clustered index/heap at all. Hence the query will be faster than if the index did not cover the query.

In SQL 2005 and higher and assuming no legacy data types, it is possible to cover any query. That doesn't necessarily mean that every query should be covered. Creating covering indexes for every query will likely result in far too many indexes. For more details on this, see the article on covering indexes

How Indexes Improve Performance

Indexes can optimize not only SELECT, but also UPDATE and DELETE statements. The reason is that SQL Server must find the records prior to modifying or deleting them. Keep in mind however, that SQL Server has to maintain clustered and non-clustered indexes every time data is INSERTED, UPDATED or DELETED. Having numerous indexes on a table can exact significant overhead on SQL Server during data modifications.

When a query is sent to SQL Server, the query optimizer comes up with the most efficient execution plan. SQL Server does not have to use an index to satisfy a query; in fact, if it is quicker to scan the entire table, SQL Server will do so. Usually if a query requires returning more than about 1% of table rows and there is no covering index it is quicker to scan the whole table than to seek on the nonclustered and then lookup the missing columns from the clustered index/heap.

Index Uniqueness

Some literature lists unique indexes as a separate type of index; however, a unique index is simply a property of a clustered or non-clustered index. Unique indexes simply ensure that all keys within an index are unique and no duplicate keys are allowed. For example, if a unique index is built on the employee_id column, no duplicate employee_ids will be allowed. If a unique index is built on employee_id and facility_id, then a combination of employee_id and facility_id must be unique.

Another way of enforcing uniqueness of values in a column is building a unique constraint on the column. When you create a unique constraint, SQL Server actually creates a unique non-clustered index on the column(s) you specify.

When a primary key is created on a column or set of columns, SQL automatically creates a unique clustered index to support the primary key, providing a clustered index does not already exist on the table. If one does then SQL creates a unique nonclustered index to enforce the primary key.

More SQLServerPedia Articles on Indexes

How SQL Server Indexes Work

• Index Overview - the basics of how indexes work.
• Data Pages & Extents - about how indexes and data are stored on disk.
• Table and Record Structure - more about the mechanics of data storage.
• Effects of Data Modifications - the more indexes you have, the longer inserts/updates/delete take.
• Index Statistics - the basics of how statistics affect query execution.
• Heaps - what are they and how do they work?

Types of Indexes in SQL Server

• Included Columns - starting with SQL Server 2005, you can add fields to indexes with a smaller performance hit.
• Indexed Views - how to build indexes on top of views for more speed.
• XML Indexes Overview - how to index XML documents and fragments.
• XML Index Rules - some additional considerations and requirements for XML indexes.

Best Practices on How to Design Database Indexes

• Indexing Strategies - choosing between clustered/nonclustered, long/short, selectivity, and more.
• Best Practices for Creating Indexes - tips to start with when designing a new index strategy.
• Choosing Index Keys - how to pick which fields to use for a primary key.
• Index Selectivity and Column Order - how to pick which columns to put first, and how it affects different kinds of queries.
• Wide Index Performance - does adding more fields to an index cause it to run slower?
• Tuning Indexes - including articles on the Index Tuning Wizard and Database Tuning Advisor.

Maintaining Indexes for Top Performance

Indexes need regular maintenance in order to perform well.

• Index Maintenance Tutorial - video and script explaining how to defragment and rebuild your indexes automatically.
• Index Maintenance in SQL Server 2005 and Beyond - SQL 2005 introduced new syntax to ALTER INDEX.
• Index Maintenance Overview - analyzing fragmentation and statistics to get the best performance possible out of your indexes.