Create an index (ADP)

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.

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

In Microsoft SQL Server databases you can create a clustered index. In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) order of the index key values. A table can contain only one clustered index. UPDATE and DELETE operations are often accelerated by clustered indexes because these operations require large amounts of data to be read. Creating or modifying a clustered index can be time-consuming, because it is during these operations that the table's rows are reorganized on disk.

Consider using a clustered index for:

• Columns that contain a limited number of unique values, such as a state column that contains only 50 unique state codes.
• Queries that return a range of values, using operators such as BETWEEN, >, >=, <, and <=.
• Queries that return large result sets.

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 the Indexes/Keys tab, and then create a new index. For more information on each dialog box option, click Help.

   To modify an existing index, select the index from the list.

4. Select the Create as CLUSTERED check box.

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