Designing an Index

When Microsoft® SQL Server™ executes a query, the query optimizer evaluates the costs of the available methods for retrieving the data and uses the most efficient method. It can perform a table scan, or it can use an index if one exists. When it performs a table scan, SQL Server starts at the beginning of the table, steps row-by-row through all the rows in the table, and extracts the rows that meet the criteria of the query. When SQL Server uses an index, it finds the storage location of the rows needed by the query and extracts only the needed rows.

When you are considering whether to create an index on a column, consider if and how an indexed column is to be used in queries. Indexes assist when a query:

• Searches for rows that match a specific search key value (an exact match query). An exact match comparison is one in which the query uses the WHERE statement to specify a column entry with a given value. For example:

  WHERE emp_id = 'VPA30890F'

• Searches for rows with search key values in a range of values (a range query). A range query is one in which the query specifies any entry whose value is between two values. For example:

  WHERE job_lvl BETWEEN 9 and 12

  or,

  WHERE job_lvl >= 9 and job_lvl <= 12

• Searches for rows in a table T1 that match, based on a join predicate, a row in another table T2 (an index nested loops join).
• Produces sorted query output without an explicit sort operation, in particular for sorted dynamic cursors.
• Scans rows in a sorted order to permit an order-based operation, such as merge join and stream aggregation, without an explicit sort operation.
• Scans all rows in a table with better performance than a table scan, due to the reduced column set and overall data volume to be scanned (a covering index for the query at hand).
• Searches for duplicates of new search key values in insert and update operations, to enforce PRIMARY KEY and UNIQUE constraints.
• Searches for matching rows between two tables for which a FOREIGN KEY constraint is defined.

Queries using LIKE comparisons can benefit from an index if the pattern starts with a specific character string, for example 'abc%', but not if the pattern starts with a wildcard search, for example '%xyz'.

In many queries, the benefits of indexes can be combined. For example, an index enables a range query in addition to covering the query. SQL Server can use multiple indexes for a single table in the same query, as well as combining multiple indexes (using a join algorithm) so that the search keys together cover a query. Additionally, SQL Server automatically determines which indexes to exploit for a query and ensures that all indexes for a table are maintained when the table is modified.

Additional Guidelines for Designing Indexes

Additional guidelines to consider when designing indexes include:

• Large numbers of indexes on a table affect the performance of INSERT, UPDATE, and DELETE statements because all indexes must be adjusted appropriately as data in the table changes. Conversely, large numbers of indexes can help the performance of queries that do not modify data (SELECT statements) because SQL Server has more indexes to choose from to determine the best way to access the data as fast as possible.
• Covered queries can improve performance. Covered queries are queries where all the columns specified in the query are contained within the same index. For example, a query retrieving columns a and b from a table that has a composite index created on columns a, b, and c is considered covered. Creating indexes that cover a query can improve performance because all the data for the query is contained within the index itself; only the index pages, not the data pages, of the table must be referenced to retrieve the data, thereby reducing overall I/O. Although adding columns to an index to cover queries can improve performance, maintaining the extra columns in the index incurs update and storage costs.
• Indexing small tables may not be optimal because it can take SQL Server longer to traverse the index searching for data than to perform a simple table scan.
• SQL Server Profiler and the Index Tuning Wizard should be used to help analyze queries and determine which indexes to create. The selection of the right indexes for a database and its workload is a very complex balancing act between query speed and update cost. Narrow indexes (indexes with few columns in the search key) require less disk space and maintenance overhead. Wide indexes, on the other hand, cover more queries. There are no simple rules for determining the right set of indexes. Experienced database administrators can often design a good set of indexes, but this task is very complex, time-consuming, and error-prone even for moderately complex databases and workloads. The Index Tuning Wizard can be used to automate this task. For more information, see Index Tuning Wizard.

Index Characteristics

After you have determined that an index is justified for a column, you can customize the type of index that best fits your situation. Characteristics of indexes include:

• Clustered vs. nonclustered
• Unique vs. nonunique
• Single-column vs. multi-column

You can also customize the initial storage characteristics of the index to optimize its maintenance by setting a fill factor, and customize its location using files and filegroups to optimize performance.

See Also