How to: Create Unique Indexes
In SQL Server, you can create a unique index on a unique column (such as employee id) or on a set of columns that together uniquely identify each record (such as author + title).
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, you create a unique index on ssn. If a user enters the same Social Security number for more than one employee, the database displays an error and cannot save the table. For more information about unique indexes, see "Unique Index Design Guidelines" on the Microsoft Web site.
When you create or modify a unique index, you can set an option to ignore duplicate keys. If this option is set to Yes and you attempt to create duplicate keys by adding data that affects multiple rows (with the INSERT statement), the row that contains a duplicate is not added. If the option is set to No, the entire operation fails, and all the data is rolled back.
Note
A new version of Table Designer appears for databases in the SQL Server 2012 format. This topic describes the old version of Table Designer, which you use with databases in earlier formats of SQL Server.
In the new version, you can change a table definition through a graphical interface or directly in a script pane. If you use the graphical interface, the table’s definition is automatically updated in the script pane. To apply the SQL code in the script pane, choose the Update button. For more information about the new version, see How to: Create Database Objects Using Table Designer.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Customizing Development Settings in Visual Studio.
To create a unique index
In Server Explorer, select the database table you want to open.
From the Database menu, click Open Table Definition.
The table opens in Table Designer.
From the Table Designer menu, click Indexes/Keys.
Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.
In the grid, click Type.
Choose Index from the drop-down list to the right of the property.
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.
In the grid, click Is Unique.
Choose Yes from the drop-down list to the right of the property.
Select the Ignore duplicate keys 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 table or 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.
Note
Not all databases work with indexes in the same way. For more information, consult your database documentation.