Developers, in Microsoft SQL Server, create a unique index on a unique column (such as product id) or on a set of columns that together uniquely identify each record (such as vendor + product).When Developers create or modify a unique index, they can set an option to ignore duplicate keys. If this option is set to Yes and they attempt to create duplicate keys by adding data that affects multiple rows (with the INSERT statement), the row containing a duplicate is not added. If it is set to No, the entire insert operation fails and all the data is rolled back. When Developers working with SQL Server “Denali”, MS SQL Server 2008 R2 and MS SQL Server 2005 want to create a unique index, they should follow the next steps:
1.In Object Explorer, right-click the table and click Design (Modify in SP1 or earlier).
The table opens in Table Designer.
2.From the Table Designer menu, click Indexes/Keys.
3.Click Add. The Selected Primary/Unique Key or Index list displays the system-assigned name of the new index.
4.In the grid, click Type.
5.Choose Index from the drop-down list to the right of the property.
6.Under Column name, select the columns they want to index. They can select up to 16 columns. For optimal performance, select only one or two columns per index. For each column they select, indicate whether the index arranges values of this column in ascending or descending order.
7.In the grid, click Is Unique.
8.Choose Yes from the drop-down list to the right of the property.
9.Select the Ignore duplicate keys option if they want to ignore data that would create a duplicate key in the unique index (with the INSERT statement).
The index is created in the database when they save the table or diagram.
Developers cannot create a unique index on a single column if that column contains NULL in more than one row. They cannot create a unique index on multiple columns if the combination of columns contains NULL in more than one row, because these are treated as duplicate values for indexing purposes.