unique constraint vs unique index in MS SQL 2000

[Kamil]

Hello

What should I use for better perfomance since
unique constraint always use index ?

Thanks
Kamil

[Erland Sommarskog]

Kamil (ka***@poczta.onet.NO.SPAM.pl) writes:

What should I use for better perfomance since
unique constraint always use index ?

It has nothing to do with performance as such, but it is a conceptual
issue.

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an
index which happens to be unique, for instance because you add the primary
key to it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Kamil]

"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn*********************@127.0.0.1...

Kamil (ka***@poczta.onet.NO.SPAM.pl) writes:

What should I use for better perfomance since
unique constraint always use index ?

It has nothing to do with performance as such, but it is a conceptual
issue.

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an
index which happens to be unique, for instance because you add the primary
key to it.

But in SQL BOL stands (Indexes->Overview):

Tip:Although a unique index will help locate information, for the best
performance results it is recommended
that you use primary key or unique constraints instead.

Thanks
Kamil

[Erland Sommarskog]

Kamil (ka***@poczta.onet.NO.SPAM.pl) writes:

But in SQL BOL stands (Indexes->Overview):

Tip:Although a unique index will help locate information, for the best
performance results it is recommended
that you use primary key or unique constraints instead.

Which is under "Visual Database Tools", supposedly written by someone who
know more about the GUI than than the engine! :-)

I counter with this paragraph from Create and Maintaining Databases ->
Indexes -> Designing an index -> Using Unique indexes:

Creating a PRIMARY KEY or UNIQUE constraint automatically creates a
unique index on the specified columns in the table. There are no
significant differences between creating a UNIQUE constraint and
creating a unique index manually. Data validation occurs in the same
manner and the query optimizer does not differentiate between a unique
index created by a constraint or created manually. A unique index and a
UNIQUE constraint cannot be created if there duplicate key values
exist.

But while the argument is wrong in the tip, the advice is correct. If you
have unique indexes on your table, but no PRIMARY KEY or UNIQUE
constraint, you have an error in your logical design. Then again, an
argument for using indexes only, and no constraints, is that if you want
to change an index, CREATE INDEX offers WITH DROP_EXISTING, which you
cannot - as far as I know - apply to a constraint. WITH DROP_EXISTING is
particularly useful when you change a clustered index, with one or more
non-clustered indexes, since if you drop and recreate, the non-clustered
indexes will be rebuilt twice.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gert-Jan Strik]

> Kamil (ka***@poczta.onet.NO.SPAM.pl) writes:

But in SQL BOL stands (Indexes->Overview):

Tip:Although a unique index will help locate information, for the best
performance results it is recommended
that you use primary key or unique constraints instead.

Kamil, as Erland noted, this is incorrect. Both will yield the same
performance. In fact, IMO one should not use constraints to try to
improve performance. In the mean time, I agree wholeheartedly with
Erland about when to use constraints.

Erland Sommarskog wrote: Then again, an
argument for using indexes only, and no constraints, is that if you want
to change an index, CREATE INDEX offers WITH DROP_EXISTING, which you
cannot - as far as I know - apply to a constraint.

Yes you can. Someone corrected me on that one a while ago. You can
recreate the unique index underlying a PRIMARY KEY or UNIQUE constraint,
as long as you don't change from clustered to nonclustered (and vice
versa). So for example, if you want to move a table to a different
filegroup you can do this be recreating the index with WITH
DROP_EXISTING.

Gert-Jan

[Erland Sommarskog]

Gert-Jan Strik (so***@toomuchspamalready.nl) writes:

Erland Sommarskog wrote:

Then again, an
argument for using indexes only, and no constraints, is that if you want
to change an index, CREATE INDEX offers WITH DROP_EXISTING, which you
cannot - as far as I know - apply to a constraint.

Yes you can. Someone corrected me on that one a while ago. You can
recreate the unique index underlying a PRIMARY KEY or UNIQUE constraint,
as long as you don't change from clustered to nonclustered (and vice
versa). So for example, if you want to move a table to a different
filegroup you can do this be recreating the index with WITH
DROP_EXISTING.

Hm, testing I find that I can use CREATE INDEX on the constraint, but
only if I don't change the columns in it. That's fine if I would like to
change the filegroup (but I never want to, because I am not that
advanced to use filegroups), but not if I need to add or remove a
column from the constraint.

So why would you want to add/remove a column from a constraint? Of course,
it happens, but with a somewhat stable data model, it should be rare. But
adding or removing a column from an index to make it covering, or more
effecient is more likely. Moral: use constraints when it describes your
data. Accidental uniqueness (becuse the PK is included), should be in an
index.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp