May 2, 2006 at 7:27 am
Hello,
Anyone know whether a unique index is faster to search than a non-unique one, and if so, why?
Regards
Gilbert
May 2, 2006 at 8:35 am
In general, Yes, a unique index should be faster than a non-unique one. This is based on size of the index, and data per page. An index having hundreds of non-unique duplicate values is not nearly as efficient as one where you can select an individual value.
Anytime you are creating an index, uniqueness should be one of the concerns that you take into account. An index which gives back hundreds of rows for a single value is not often very useful. Afterall, the idea of an index is to help you with quickly identifying a record, so if it doesn't do that, how much use it is really?
May 4, 2006 at 1:43 am
Yes, thanks for the reply, but i left out a crucial point in my question, i am comparing two indexes with exactly the same fields on a table, one specified unique and the other not. Wondering if SQL server changes the way it searches if it knows the index is unique. There is no difference between query plans.
May 4, 2006 at 4:16 am
the selectivity is the important factor, technically uniuqe is a property of the index. I guess in the search for suitable indexes the optimiser may consider a unique over a non-unique, if you have both then you should know which one it has chosen!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
May 4, 2006 at 5:09 am
To expand upon. In deciding what index to use for a search it is based on selectivity statistic of how unidque the index is. It doesn't factor in if it is a unique or non-unique index.
Now what makes the difference is this. At insert or update time a Unique index provides the controlling factor against the data to ensure uniqueness. Of course a non-unique will not control what records go in unless you hav some other mechanism such as a Trigger checkin for uniqueness to override that fact.
May 8, 2006 at 11:40 am
Although i see no speed differences, SQL Server (2005) seems to consistently choose the unique index over the non-unique for searches. In this case i think i would go for the non-unique index as there is no checking this index on insert, update for existing values - though that check should be very quick as the item must be inserted in the correct position in the index anyway. This, of course, considering that uniqueness IS enforced another way. Thanks for the input.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply