I 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.
I 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.
Thanks
Paul Randal Dev Lead, Microsoft SQL Server Storage Engine
Unique Constraint And Unique Index
Posted - 06/22/2005 : 08:54:37
