Sunday, December 06, 2009

Unique Indexes versus Unique Keys (Sql Server 2005)

I originally posted this article a few weeks ago, but pulled it after a day or two as I discovered I had made an error in my testing which invalidated some of my conclusions. I have now updated the article and reposted it, if you read the original please disregard it and read this version instead.

During a recent meeting with my colleagues, the question was raised, “What is the difference between a unique index and a unique key in Sql Server ?”. It turns out nobody really knew, and some people always used one, others used the other, and others had semi-arbitrary reasons for choosing one over the other.

After the meeting I jumped on the internet and tried to find out the answer, unfortunately it wasn’t that easy. Most of the answers said that a unique index and a unique constraint are the same but you use a constraint to indicate the data is unique in itself (i.e. social security numbers, which it turns out aren’t actually unique anyway).

However I wasn’t convinced a constraint was the same as a key (it seems that in terms of unique ones they are very similar), or that if they were the same that there were no functional differences between a unique index and a unique key/constraint. So to that end, I decided to do some testing myself. I have only done very basic checks, and only comparing indexes and keys, not specifically constraints. Here’s what I’ve found (at least as far as Sql 2005 goes);

Unique Keys Are More Unique

Unique indexes allow multiple rows with null values, unique keys require each row to be unique including which columns contain nulls. The most simple case is an index and constraint with a single column in each. The index will allow you to have multiple rows where that column is null, the key will only allow a single row where that column is null.

In the case of multiple columns I presume the index is fairly easy going, so long as all the values in all the columns that aren’t null are unique then it will allow the row to be added as well as multiple rows where all the columns are unique. In the case of a key, the same column might be allowed to be null in multiple rows so long as the combination of other columns in the key can uniquely identify that row.

Unique Keys Can Be Used In Foreign Keys

A column that has a unique key on it can be used in a foreign key constraint, however a column with a unique index cannot be (unless perhaps it has a unique constraint as well). Presumably this is related to the first issue, in that a unique key actually guarantees a single row can be found where as a unique index might return multiple rows where null values are involved.

Sql Management Studio GUI Differences

First of all, keys, indexes and constraints are all in separate places in the Object Explorer window of Sql Management Studio. Once you expand the parent table, there are sub-nodes for each (keys, indexes, constraints) and each item is listed under the appropriate category. This is a minor difference but does mean you might have to look in multiple places to find the item you’re looking for if you don’t know what type it is.

Secondly, keys seem to be the poorer cousin to indexes in terms of access to features and related information through Sql Management Studio. When you right click on an index you get options to Rebuild or Reorganise the index, both of which effectively ‘defrag’ the index data in the database and can optimise performance. In the ‘properties’ of the index you can also seem information about how fragmented it is.

Keys do not have either of these options, nor any properties that can be accessed via the GUI. It seems you can run the T-Sql commands to rebuild or reorganise the keys, likewise setting options like storage locations, sorting in tempdb, and index locking options can all be applied when the key is created via T-Sql but do not seem to be available when creating a key via the GUI.

Some options are only available on indexes and not on keys, whether using T-Sql or the GUI. These features include ‘disabling’ an index, and ‘included columns’ which can be used to improve the performance of read based queries at the expense of space used and insert/update performance.

Deleting keys and indexes is also different. Using T-Sql you seem to be able to delete either fine. Using the GUI deleting a unique index is no problem, but deleting a unique key often fails with an error saying the key is being used to enforce uniqueness in the table and the delete is cancelled. I’m not sure exactly what causes this but I think it’s as simple has having rows in the table at the time you try to delete the key.

Similarities

Both unique indexes and unique keys are used by the optimiser the same way, that is, it is not necessary to create a unique index and a unique key for performance reasons. Also, both keys and indexes automatically create a set of matching ‘statistics’ used by the optimiser to create the best query plan possible.

Columns with unique indexes or keys can also both be used in foreign keys, there is no difference between them as far as foreign keys are concerned.

Conclusion

Unique indexes and unique keys are extremely similar and for common purposes there is very little difference, but they are not exactly the same in detail. Differences in management ability via the Sql Management Studio GUI as well as advanced performance options such as included columns can be important in edge cases.

If anyone knows of any more differences, or any additional detail, please post a comment to let me and others know.


No comments:

Post a Comment