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.
If the columns in a WHERE clause use the equals operator (=) with leading columns in the index, the query optimizer uses the unique index to process the query. This is because few rows will fit the query, so using the index has a minimal fixed cost. For example, if the query searched for the last_name and first_name columns, the full_name index would be used even though the middle_initial column is not specified.
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 (where 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.
Note Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns in the table.
| Creating an Index | Index Tuning Wizard |