Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
rajender
Starting Member
13 Posts |
 Posted - 2005-06-22 : 08:54:37
|
| Hi TeamI want to know, while inserting multiple records in a table under one transaction at what time a unique constraint and a unique index will check for uniqueness. As Books online suggests to create Unique constraint instead of Unique index, it gave me some idea, perhaps unique key checks uniqueness after every insert while unique index verifies after inserting all records. But i still want to know the functionality of the both. Thanks in advance.Rajender Kr. |
|
|
nieurig
Starting Member
8 Posts |
Posted - 2005-06-22 : 09:18:28
|
| Hello Rajender,both check will check in that moment you inserting a (one) record (for each record)Niels |
 |
|
|
paulrandal
Yak with Vast SQL Skills
899 Posts |
Posted - 2005-06-22 : 21:00:51
|
quote:
Originally posted by rajender
Hi TeamI want to know, while inserting multiple records in a table under one transaction at what time a unique constraint and a unique index will check for uniqueness. As Books online suggests to create Unique constraint instead of Unique index, it gave me some idea, perhaps unique key checks uniqueness after every insert while unique index verifies after inserting all records. But i still want to know the functionality of the both. Thanks in advance.Rajender Kr.
They both end up creating a unique index (i.e. they're exactly the same under the covers). From BOL:There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. By doing this the objective of the index will be clear.ThanksPaul RandalDev Lead, Microsoft SQL Server Storage Engine |
|
|
|
|
|
|
Unique Constraint And Unique Index