SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Difference between UNIQUE CONSTRAINT versus UNIQUE INDEX

11 February,2013 by Jack Vamvas

Question: Is there a difference between a UNIQUE CONSTRAINT versus UNIQUE INDEX?

Answer: Creating a UNIQUE CONSTRAINT automatically creates a UNIQUE INDEX. This makes a UNIQUE CONSTRAINT and UNIQUE INDEX similar.

The query optimizer interprets an explicitly created UNIQUE INDEX or a CONSTRAINT created index as the same.

Data is validated in the same way and both check for unique data

Deciding when to implement a UNIQUE CONSTRAINT versus a UNIQUE INDEX is an issue of design. A UNIQUE CONSTRAINT is part of the database design logical view. A UNIQUE INDEX is part of the database design physical view

 

--how to create a unique constraint
ALTER TABLE myTable ADD CONSTRAINT
            myConstraint UNIQUE NONCLUSTERED
    (
               A_col
    )

--how to create a unique index
CREATE UNIQUE INDEX uix_my_unique_index ON myTable(a_col)

 

 

Read More

SQL Table variables do not support indexes MYTH - SQL Server DBA

SQL SELECT INTO - SQL Server DBA

Modify a SQL CHECK CONSTRAINT - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer