Creating an Index

After the design has been determined, indexes can be created on your tables in the database.

Microsoft® SQL Server™ automatically creates unique indexes to enforce the uniqueness requirements of PRIMARY KEY and UNIQUE constraints. Unless a clustered index already exists on the table or a nonclustered index is explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.

If you need to create an index that is independent of a constraint, you can use the CREATE INDEX statement. By default, a nonclustered index is created if the clustering option is not specified.

Additional considerations:

• Only the owner of the table can create indexes on the same table.
• Only one clustered index can be created per table.
• The maximum number of nonclustered indexes that can be created per table is 249 (including any indexes created by PRIMARY KEY or UNIQUE constraints).
• The maximum length of all the columns that comprise the index is 900 bytes. For example, a single index could not be created on three columns defined as char(300), char(300), and char (301) because the total width exceeds 900 bytes.
• The maximum number of columns that can comprise the same index is 16.

When you create indexes with the CREATE INDEX statement, you must specify the name of the index, table, and columns to which the index applies. New indexes created as part of a PRIMARY KEY or UNIQUE constraint or using SQL Server Enterprise Manager are automatically given system-defined names based on the database table name. If you create multiple indexes on a table, the index names are appended with _1, _2, and so on. The index can be renamed if necessary.

Note It is not possible to create an index in the current database while the current database is being backed up.

Clustered Indexes

When you create a clustered index, the table is copied, the data in the table is sorted, and then the original table is deleted. Therefore, enough empty space must exist in the database to hold a copy of the data.

By default, the data in the table is sorted when the index is created. However, if the data is already sorted because the clustered index already exists and is being re-created using the same name and columns, the sort operation can be automatically skipped by rebuilding the index rather than creating the index again from the beginning. The rebuild operation checks that the rows are sorted while building the index. If any rows are not correctly sorted, the operations cancels and the index is not created.

Unique Indexes

Creating a unique index ensures that any attempt to duplicate key values fails. If a single query is created that causes duplicate and nonduplicate key values to be added, SQL Server rejects all rows, including the nonduplicate key values. For example, if a single insert statement retrieves 20 rows from table A and inserts them into table B, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, the IGNORE_DUP_KEY clause can be specified when creating the index that causes only the duplicate key values to be rejected; the nonduplicate key values are added. In the previous example, only the 10 duplicate key values would be rejected; the other 10 nonduplicate key values would be inserted into table B.

A unique index cannot be created if there are any duplicate key values. For example, if you want to create a unique, composite index on columns a and b, but there are two rows in the table that contain the values 1 and 2 for a and b respectively, the unique index cannot be created.

Note You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes.

To create an index when creating a table

To create an index on an existing table

You can also create an index using the Create Index Wizard in SQL Server Enterprise Manager.

To create an index using the Create Index Wizard

See Also