Committed with SQL

SQL Server– Unique Constraint VS Unique Index

Both Unique constraint and Unique Index enforces uniqueness of the column. When we define an Unique constraint, SQL server creates an Unique non clustered index on the column where Unique constraint is defined.

Even though both of them can be used for same purpose but consider below points before you decide which one to use.

1 -  From SQL server 2008 onwards we have Filtered index , Include columns which can’t be define on index which is created while defining Unique constraint but we can define it while defining Unique Index. We can have better control over the Unique index.

2 -  Derived from point 1, sometime we need our column should be unique but it should allow multiple NULL values in that case by implementing Unique filtered Index we can achieve it. Also we can avoid Key lookup by adding included columns in an Unique Index but not in Unique constraint.

3 -  Unique constraint always creates non clustered index. Unique Index can be defined either Clustered or non Clustered.

4 -  You can’t drop only the index associated with the Unique constraint.

Leave your comment, If you have any points which we should consider before we decide which one to use between Unique Constraint and Unique Index.


If you liked this post, do like on Facebook at https://www.facebook.com/s4sql

About these ads

August 28, 2012 - Posted by | SQL Objects, SqlCommitted, Transact-SQL | , , , , ,

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


Get every new post delivered to your Inbox.

Join 76 other followers

%d bloggers like this: