473,324 Members | 2,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

unique constraint vs unique index in MS SQL 2000

Hello

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

Thanks
Kamil
Jul 20 '05 #1
5 10832
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
Jul 20 '05 #2
"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
Jul 20 '05 #3
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
Jul 20 '05 #4
> 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
Jul 20 '05 #5
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
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

26
by: Agoston Bejo | last post by:
I want to enforce such a constraint on a column that would ensure that the values be all unique, but this wouldn't apply to NULL values. (I.e. there may be more than one NULL value in the column.)...
3
by: June Moore | last post by:
Hi, I would like to add a unique index that consists of two fields in a table. e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination must be Unique. Can anyone tell me the...
4
by: Q. John Chen | last post by:
All, What's the difference between a unique contraint and unique? sementically, if you want a column contain unique values, it is a contraint. And an index is for searching/sort. The questions...
4
by: Dave | last post by:
Can you create a unique constraint on multiple columns, or does it have to be implemented as a unique index? If possible can someone please post some sample code? Thanks,
1
by: Rajesh Kumar Mallah | last post by:
Hi , Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint. the only way is to add the constriant is using UNIQUE INDEX . Is it a bug or intended behaviour? ...
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
5
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a...
10
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.