Indexing - Uniqueness vs Highly uplicate

[Puvendran Selvaratnam]

Hi,

First of all my apologies if you have seen this mail already but I am
re-sending as there were some initial problems.

This query is related to defining indexes to be unique or not and
consequences thereof.

Some documented facts that I am aware of include

1. Defining uniqueness allows optimiser to create optimal plans eg
select based on keys in such an index allows the optimiser to determine
at most only one row will be returned

2. Defining uniqueness ensures that rule (business/Primary key) is
enforced, regradless of how the data is entered.

We have many cases where non unique indexes are defined. The approach to
date has been that even though we are aware of some of the benefits
offered by defining uniqueness , we have chosen not to add keys to non
unique indexes such that they become unique. The primary reason for this
was that we did not want to make the keys comprising the indexes
unnecessarily large and therefore ensuing consequences when DML
statements are performed.

However, I have concerns that having highly duplicate indexes can have
performance impacts , including deadlocking. I am also aware Sybase used
to store duplicate values in overflow pages and therefore there were
performance consequences. Could SQL 2000 have the same behaviour ?

Thanking you in advance

Puvendran


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Re: Indexing - Uniqueness vs Highly uplicate

Puvendran Selvaratnam (puvendran.selv aratnam@btfinan cialgroup.com) writes:[color=blue]
> 1. Defining uniqueness allows optimiser to create optimal plans eg
> select based on keys in such an index allows the optimiser to determine
> at most only one row will be returned
>
> 2. Defining uniqueness ensures that rule (business/Primary key) is
> enforced, regradless of how the data is entered.[/color]

If you want to enforce uniqueness that comes from business rules, you
should use PRIMARY KEY and UNIQUE constraints. Not that it matters in
terms of performance; the constraints are just a different name for an
index. But because it gives you information about your database. Plus
that you can add a foreign-key constraint that refers to a PK or UNIQUE
constraint, but not a plain index.
[color=blue]
> We have many cases where non unique indexes are defined. The approach to
> date has been that even though we are aware of some of the benefits
> offered by defining uniqueness , we have chosen not to add keys to non
> unique indexes such that they become unique. The primary reason for this
> was that we did not want to make the keys comprising the indexes
> unnecessarily large and therefore ensuing consequences when DML
> statements are performed.[/color]

This strategy is correct.

Note also, that in the end the index will always be unique in SQL Server.
For a non-unique clustered index, SQL Server adds a 32-bit "uniquifier " to
the index. For this reason, I tend to add the PK to a clustered index,
if it is 32 bits, not if is longer.

For a non-clustered index, the row locator is the key value of the
clustered index. (If there is no clustrered index, the row locator will
be a row id.)

Thus, the columns of the clustered index will always be there, at least
in the leaf level of the index. Note that this also means that a wide
cluster index, will also have an impact on the non-clustered index.
[color=blue]
> However, I have concerns that having highly duplicate indexes can have
> performance impacts , including deadlocking. I am also aware Sybase used
> to store duplicate values in overflow pages and therefore there were
> performance consequences. Could SQL 2000 have the same behaviour ?[/color]

Yes, up do SQL 6.5, SQL Server had overflow pages for non-unique
clustered index, but as indicated above, this is no longer the case.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp