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 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.
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 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.
ON
A warning message will occur when duplicate key values are inserted into a unique index. Only the rows violating the uniqueness constraint will fail.OFF
An error message will occur when duplicate key values are inserted into a unique index. The entire INSERT operation will be rolled back.
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.
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