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.
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.
Index independent of a constraint
Multiple unique nonclustered indexes can be defined on a table.
For more information, see CREATE INDEX (Transact-SQL).
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.
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 LastNamecolumns, 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.
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 when an insert operation attempts to insert duplicate key values into a unique index. The IGNORE_DUP_KEY option applies only to insert operations after the index is created or rebuilt. The option has no effect when CREATE INDEX, ALTER INDEX, or UPDATE is executed. The default is OFF.
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 ON, only the 10 duplicate key values will be rejected; the other 10 nonduplicate key values will be inserted into the table.
IGNORE_DUP_KEY cannot be ON for indexes created on a view, non-unique indexes, XML indexes, spatial indexes, and filtered indexes.
To view IGNORE_DUP_KEY, use sys.indexes.
In backward compatible syntax, WITH IGNORE_DUP_KEY is equivalent to WITH IGNORE_DUP_KEY = ON.
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.