SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Unique Constraint And Unique Index
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajender
Starting Member

India
13 Posts

Posted - 06/22/2005 :  08:54:37  Show Profile  Visit rajender's Homepage  Reply with Quote
Hi Team

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.

nieurig
Starting Member

8 Posts

Posted - 06/22/2005 :  09:18:28  Show Profile  Reply with Quote
Hello Rajender,

both check will check in that moment you inserting a (one) record (for each record)

Niels
Go to Top of Page

paulrandal
Yak with Vast SQL Skills

USA
899 Posts

Posted - 06/22/2005 :  21:00:51  Show Profile  Visit paulrandal's Homepage  Reply with Quote
quote:
Originally posted by rajender

Hi Team

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000