Creating Unique Indexes

Creating a unique index guarantees that any attempt to duplicate key values fails. There are no significant differences between creating a UNIQUE constraint and creating a unique index that is independent of a constraint. Data validation occurs in the same manner, and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE constraint on the column when data integrity is the objective. This makes the objective of the index clear.

Typical Implementations

Unique indexes are implemented in the following ways:

• PRIMARY KEY or UNIQUE constraint
  When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.
  When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.
  For more information, see PRIMARY KEY Constraints and UNIQUE Constraints.
• Index independent of a constraint
  Multiple unique nonclustered indexes can be defined on a table.
  For more information, see CREATE INDEX (Transact-SQL).
• Indexed view
  To create an indexed view, a unique clustered index is defined on one or more view columns. The view is executed (materialized) and the result set is stored in the leaf level of the index in the same way table data is stored in a clustered index. For more information, see Creating Indexed Views.

Resolving Duplicate Value Problems

A unique index or constraint cannot be created if there are existing duplicate values in the key columns. For example, if you want to create a unique composite index on columns FirstName and LastName, but there are two rows in the table that contain the values 'Jane' and 'Smith' in the FirstName and LastName columns, the unique index cannot be created. The problem can be resolved in one of the following ways:

• Add or remove columns in the index definition to create a unique composite. In the previous example, adding a MiddleName column to the index definition might resolve the duplication problem.
• If the duplicate values are the result of data entry errors, manually correct the data and then create the index or constraint.

Using the IGNORE_DUP_KEY Option to Handle Duplicate Values

When you create or modify a unique index or constraint, you can set the IGNORE_DUP_KEY option ON or OFF. This option specifies the error response to duplicate key values in a multiple-row INSERT statement after the index has been created. When IGNORE_DUP_KEY is set to OFF (the default), the SQL Server 2005 Database Engine rejects all rows in the statement when one or more rows contain duplicate key values. When set to ON, only the rows that contain duplicate key values are rejected; the nonduplicate key values are added.

For example, if a single statement inserts 20 rows into a table with a unique index, and 10 of those rows contain duplicate key values, by default all 20 rows are rejected. However, if the index option IGNORE_DUP_KEY is set to ON, only the 10 duplicate key values will be rejected; the other 10 nonduplicate key values will be inserted into the table.

The option setting is stored in the metadata of the index. To display the current setting, use the sys.indexes catalog view.

Handling NULL Values

For indexing purposes, NULL values compare as equal. Therefore, you cannot create a unique index, or UNIQUE constraint, if the key values are NULL in more than one row. Select columns that are defined as NOT NULL when you choose columns for a unique index or unique constraint.

Disk Space Requirements

The process of determining disk space requirements for unique indexes is the same as that of clustered and nonclustered indexes. For information about disk space requirements for indexes, see Determining Index Disk Space Requirements.

To create an index when you create a table

CREATE TABLE (Transact-SQL)

To create an index on an existing table

CREATE INDEX (Transact-SQL)

See Also

Concepts

Fill Factor
index create memory Option
Modifying Indexes
Placing Indexes on Filegroups
PRIMARY KEY Constraints
UNIQUE Constraints

Other Resources

ALTER INDEX (Transact-SQL)
sys.indexes (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance