By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,965 Members | 1,757 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,965 IT Pros & Developers. It's quick & easy.

Indexing - Uniqueness vs Highly uplicate

P: n/a

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!
Jul 20 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Puvendran Selvaratnam (pu*******************@btfinancialgroup.com) writes:
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.
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.
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.
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.
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 ?


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, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.