SQLcommitted

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

August 28, 2012 Posted by SQLcommitted | SQL Objects, SqlCommitted, Transact-SQL | Filtered Index, SQLcommitted, T-SQL, Transact-SQL, Unique Constraint, Unique Index | Leave a Comment