UNIQUE CONSTRAINT vs UNIQUE INDEX, differences
-
Where can I find the difference of a unique constraint with a unique index, is there a performace benefit? When should I use one or the other?
-
The difference is declarative versus physical/procedural integrity.
With a constraint, you specify what integrity you want, by declaring it as part of the table definition, and you allow the DBMS to implement it.
With a unique index (or trigger, or procedure) you specify how to physically implement it.
In SQL Server they are currently identical performance-wise because SQL Server creates a unique index behind the scenes to implment the constraint. Doesn't mean this will always be the case in future version.
FYI, here's an old thread from the MS newsgroups on the same subject:
-
besides constraints can be disabled indexes can't (on 2005 you are able to disable indexes, that's cool )
HTH
* Noel -
I have never seen in an execution plan SQL use the behind the scenes index created on a constraint. If it does what would it look like? I was always told that constraints will not perform as an index unless it's the primary key contraint. Do Foreign Key constraints behave differently?
-
a unique constraint create an index which is used in searches. A foreign key does not create an index.
regards,
Mark Baekdal
+44 (0)208 241 1762
Database change management for SQL Server
-
mark baekdal posted: >>Unique constraints do not allow nulls, <<
Incorrect, Uinque Constraints allow for ONE NULL
create table test (d int )
Alter Table Test Add constraint UQ_d UNIQUE (d)
insert into test(d) values(null)
select * from test
d
-----------
NULL
HTH
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply