Export (0) Print
Expand All

Using Unique Indexes

SQL Server 2000

A unique index ensures that the indexed column contains no duplicate values. In the case of multicolumn unique indexes, the index ensures that each combination of values in the indexed column is unique. For example, if a unique index full_name is created on a combination of last_name, first_name, and middle_initial columns, no two people could have the same full name in the table.

Both clustered and nonclustered indexes can be unique. Therefore, provided that the data in the column is unique, you can create both a unique clustered index and multiple-unique nonclustered indexes on the same table.

Considerations

Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. If uniqueness must be enforced to ensure data integrity, create a UNIQUE or PRIMARY KEY constraint on the column rather than a unique index. For example, if you plan to query frequently on the Social Security number (ssn) column in the employee table (in which the primary key is emp_id), and you want to ensure that Social Security numbers are unique, create a UNIQUE constraint on ssn. If the user enters the same Social Security number for more than one employee, an error is displayed.

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table. There are no significant differences between creating a UNIQUE constraint and creating a unique index manually. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or created manually. A unique index and a UNIQUE constraint cannot be created if there duplicate key values exist.

Creating a unique index instead of non-unique on the same combination of columns provides additional information for the query optimizer; therefore, creating a unique index is preferred.

See Also

Creating an Index

Index Tuning Wizard

Show:
© 2014 Microsoft