A table may have alternate candidate keys that uniquely identify each occurrence in the table. A staff or employee table, for example, may have a Staff Identifier and a National Insurance number entered for all staff. Each of these fields is unique to each occurrence and creating a unique index constraint on the field will ensure that duplicate values do not occur.
Indexes may be defined on a single field or a set of fields as a composite key which may be useful for optimising queries or for sorting data for a report or batch processing tasks.
If several fields are often involved in a query selection is good for performance to create a composite key. If some selections only use one or two of the fields then they should be defined as the first columns of the index otherwise the index will not be selected by the query optimiser.
ALTER TABLE person
ADD CONSTRAINT IX_person
UNIQUE NONCLUSTERED (id)
A single clustered index may be defined on a table that physically sorts the records into the index order. This can speed up performance on a table if sequential access to a set of records is often needed in the sequence of the clustered index. Retrieval of individual records is not improved by a clustered index.
Take care not to cause contention problems when creating a clustered index. Many users entering new records simultaneously with similar clustered index values will cause a performance bottle-neck as they all need to access the same part of the clustered index. This occurs particularly with date or timestamp values or incrementing primary keys when they are used as clustered indexed. The situation has improved with SQL Server 6.5 but is still not recommended.
There are more advanced options, discussed below, that can be defined against an index but are initially best avoided as they may result in part of a transaction failing with no indication to the user that some records have been ignored by the database engine.
Indexes are defined with the Manage Indexes window available by rightclicking on the required Table in the SQL Enterprise Manager and selecting the Indexes option. The fields for the Table are displayed and can be moved into the Index by clicking the Add button. More than one field can be added to create composite keys.
Manage Indexes Window
SQL Server indexes can be defined to ignore certain problems when inserting new records into a table. The Ignore Duplicate Row option causes records with duplicate rows, where a clustered index has been defined, to be ignored during a transaction without failing the whole transaction. In this case, duplicate rows are not inserted into the table but the remaining records are processed.
Similarly with the Ignore Duplicate Key option, attempts to insert a record with a duplicate key that has been defined as a unique index, will ignore only that record and continue with the remainder of the transaction.
The Allow Duplicate Rows option contradicts the principle of a primary key for each record and is required only in unusual circumstances.
Relational database tables should theoretically always have a unique primary key and therefore no duplicate rows. Microsoft Access, for example, will not allow updates on a table without a primary key.
Clustered Indexes allow for the Sorted Data checkbox to be specified so processing time is not wasted sorting the Index. The Index is not created however if the data is not sorted correctly.