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.
Note
IGNORE_DUP_KEY cannot be specified for an index created on a view or for an XML index.
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
To create an index on an existing table
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)