Creating Indexes (Database Engine)

This topic describes major index creation tasks and provides implementation and performance guidelines to consider before you create an index.

Index Creation Tasks

The following tasks make up our recommended strategy for creating indexes:

1. Design the index.
   Index design is a critical task. Index design includes determining which columns to use, selecting the index type (for example, clustered or nonclustered), selecting appropriate index options, and determining filegroup or partition scheme placement. For more information, see Designing Indexes.
2. Determine the best creation method. Indexes are created in the following ways:
   • By defining a PRIMARY KEY or UNIQUE constraint on a column by using CREATE TABLE or ALTER TABLE
     The SQL Server 2005 Database Engine automatically creates a unique index to enforce the uniqueness requirements of a PRIMARY KEY or UNIQUE constraint. By default, a unique clustered index is created to enforce a PRIMARY KEY constraint, unless a clustered index already exists on the table, or you specify a unique nonclustered index. By default, a unique nonclustered index is created to enforce a UNIQUE constraint unless a unique clustered index is explicitly specified and a clustered index on the table does not exist.
     Index options and index location, filegroup or partition scheme, can also be specified.
     An index created as part of a PRIMARY KEY or UNIQUE constraint is automatically given the same name as the constraint name. For more information, see PRIMARY KEY Constraints and UNIQUE Constraints.
   • By creating an index independent of a constraint by using the CREATE INDEX statement, or New Index dialog box in SQL Server Management Studio Object Explorer
     You must specify the name of the index, table, and columns to which the index applies. Index options and index location, filegroup or partition scheme, can also be specified. By default, a nonclustered, nonunique index is created if the clustered or unique options are not specified.
3. Create the index.
   Whether the index will be created on an empty table or one that contains data is an important factor to consider. Creating an index on an empty table has no performance implications at the time the index is created; however, performance will be affected when data is added to the table.
   Creating indexes on large tables should be planned carefully so database performance is not hindered. The preferred way to create indexes on large tables is to start with the clustered index and then build any nonclustered indexes. Consider setting the ONLINE option to ON when you create indexes on existing tables. When set to ON, long-term table locks are not held enabling queries or updates to the underlying table to continue. For more information, see Performing Index Operations Online.

Implementation Considerations

The following table lists the maximum values that apply to clustered, nonclustered, and XML indexes. Unless specified, the limitations apply to all index types.

Maximum index limits	Value	Additional information
Clustered indexes per table	1
Nonclustered indexes per table	249	Includes nonclustered indexes created by PRIMARY KEY or UNIQUE constraints, but not XML indexes.
XML indexes per table	249	Includes primary and secondary XML indexes on xml data type columns. Indexes on xml Data Type Columns
Number of key columns per index	16*	Maximum Size of Index Keys. Clustered index is limited to 15 columns if the table also contains a primary XML index.
Maximum index key record size	900 bytes*	Does not pertain to XML indexes. Maximum Size of Index Keys.

*You can avoid index key column and record size limitations of nonclustered indexes by including nonkey columns in the index. For more information, see Index with Included Columns.

Data Types

Generally, any column in a table or view can be indexed. The following table lists data types that have restricted index participation.

Data type	Index participation	Additional information
CLR user-defined type	Can be indexed if the type supports binary ordering.	Working with CLR User-defined Types
Large object (LOB) data types: image, ntext, text, varchar(max), nvarchar(max), varbinary(max), and xml	Cannot be an index key column. However, an xml column can be a key column in a primary or secondary XML index on a table. Can participate as nonkey (included) columns in a nonclustered index except for image, ntext, and text. Can participate if part of a computed column expression.	Index with Included Columns Indexes on xml Data Type Columns
Computed columns	Can be indexed. This includes computed columns defined as method invocations of a CLR user-defined type column, as long as the methods are marked deterministic. Computed columns derived from LOB data types can be indexed either as a key or nonkey column as long as the computed column data type is allowed as an index key column or nonkey column.	Creating Indexes on Computed Columns
Varchar columns pushed off-row	The index key of a clustered index cannot contain varchar columns that have existing data in the ROW_OVERFLOW_DATA allocation unit. If a clustered index is created on a varchar column and the existing data is in the IN_ROW_DATA allocation unit, subsequent insert or update actions on the column that would push the data off-row will fail.	Table and Index Organization Row-Overflow Data Exceeding 8 KB

Additional Considerations

The following are some additional considerations for creating an index:

• You can create an index, if you have CONTROL or ALTER permission on the table.
• When created, the index is automatically enabled and available for use. You can remove access to an index by disabling it. For more information, see Disabling Indexes.

Disk Space Requirements

The amount of disk space required to store the index depends on the following factors:

• The size of each data row in the table and the number of rows per page. This determines the number of data pages that must be read from disk to create the index.
• The columns in the index and the data types used. This determines the number of index pages that have to be written to disk. For more information, see Estimating the Size of a Clustered Index and Estimating the Size of a Nonclustered Index.
• Temporary disk space required during the index creation process. For more information, see Determining Index Disk Space Requirements.

Performance Considerations

The time taken to physically create an index is largely dependent on the disk subsystem. Important factors to consider are the following:

• The recovery model of the database. The bulk-logged recovery model provides greater performance and reduced log-space consumption during the index creation operation than full recovery. However, bulk-logged recovery reduces the flexibility for point-in-time recovery. For more information, see Choosing a Recovery Model for Index Operations.
• The RAID (redundant array of independent disks) level used to store the database and transaction log files. Generally, RAID levels that use striping will have better I/O bandwidth.
• Number of disks in the disk array, if RAID was used. More drives in the array increases data transfer rates proportionally.
• Where the intermediate sort runs of the data is stored. Using the SORT_IN_TEMPDB option can reduce the time that is required to create an index when tempdb is on a different set of disks than the user database. For more information, see tempdb and Index Creation.
• Creating the index offline or online.
  When an index is created offline (the default), exclusive locks are held on the underlying table until the transaction creating the index has completed. The table is inaccessible to users while the index is being created.
  In SQL Server 2005, you can specify the index be created online. When the online option is set to ON, long-term table locks are not held, enabling queries or updates to the underlying table to continue while the index is being created. Although we recommend online index operations, you should evaluate your environment and specific requirements. It may be better to run index operations offline. In doing this, users have restricted access the data during the operation, but the operation finishes faster and uses fewer resources. For more information, see Performing Index Operations Online.

To create a PRIMARY KEY or UNIQUE constraint when you create a table

CREATE TABLE

To create a PRIMARY KEY or UNIQUE constraint on an existing table

ALTER TABLE

To create an index

CREATE INDEX

See Also

Concepts

Creating Clustered Indexes
Creating Indexes with Included Columns
Creating Nonclustered Indexes
Creating Unique Indexes
Implementing Partitioned Tables and Indexes
PRIMARY KEY Constraints
UNIQUE Constraints
Indexes on xml Data Type Columns

Other Resources

ALTER TABLE (Transact-SQL)
CREATE TABLE (Transact-SQL)
Getting Started with Full-Text Search

Help and Information

Getting SQL Server 2005 Assistance