Create a Unique Index

Background information

In SQL Server, you can create a unique index when uniqueness is a characteristic of the data itself, but the combination of indexed columns is not the same as the tableÆs primary key. 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 Social Security numbers are unique, create a unique index on ssn. If the user enters the same Social Security number for more than one employee, the database displays an error and cannot save the table.

When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set and you attempt to create duplicate keys by adding or updating data that affects multiple rows (with the INSERT or UPDATE statement), the row that causes the duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where "Jones" already exists, you end up with one "Jones" and no "Smith" in the resulting table. The original "Smith" row is lost because an UPDATE statement is actually a DELETE followed by an INSERT. "Smith" was deleted and the attempt to insert an additional "Jones" failed. The whole transaction cannot be rolled back because the purpose of this option is to allow a transaction in spite of the presence of duplicates.

Create a unique index

  1. In the Database window, click Database Diagrams   under Objects, click the database diagram you want to open, and then click Design on the database window toolbar.
  2. In your database diagram, select the table you want to index, right-click the table, and choose Properties from the shortcut menu.
  3. Choose Indexes/Keys. The Selected index list displays the system-assigned name of the new index.
  4. Under Column name, select the columns you want to index. You can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column you select, indicate whether the index arranges values of this column in ascending or descending order.
  5. Select the Create UNIQUE check box.
  6. Select the Index option.
  7. Select the Ignore duplicate key option if you want to ignore new or updated data that would create a duplicate key in the index (with the INSERT or UPDATE statement).

The index is created in the database when you save the database diagram.

Note   You cannot create a unique index on a single column if that column contains NULL in more than one row. Similarly, you cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row. These are treated as duplicate values for indexing purposes