7

We have two columns is_unique and is_unique_constraint for unique constraints in sys.indexes and I want to clarify few concepts based on these two columns

  1. Create statement for both types of indexes are same except fill factor property value or do I missing something?
  2. If statement 1 is true then why we have two columns in sys.indexes for unique constraint?
  3. When is_unique=1 is_unique_constraint =0 and then drop statement for index is
    DROP INDEX [index name] ON [dbo].[TableName] WITH ( ONLINE = OFF )

    And when is_unique=1 is_unique_constraint =1 and then drop statement for index is
    ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [IndexName]

    Why is that so?

  4. Where we use unique index and where unique constraint, what is best practice?

2 Answers 2

4

Fact is that there is no practical difference between a unique constraint and a unique index.

  1. Querying sys.objects, you will find unique constraint is listed as a constraint object and its related index can be found while querying sys.indexes, where it is marked is_unique = 1 as well as is_unique_constraint = 1. On other hand, for a unique indexes is_unique_constraint value will be zero (0).
  2. While creating unique constraint you can use Fill Factor option as compare to unique index where you have more options like FILLFACTOR, PAD_INDEX, IGNORE_DUP_KEY, DROP_EXISTING, and STATISTICS_NORECOMPUTE.
  3. Unique constraint is maintained through a unique index
  4. Just like Default and Primary Key, a unique constraint can't be disabled using NOCHECK syntax
  5. To delete a unique index with is_unique_constraint = 1, it is must to delete it through DROP CONSTRAINT syntax instead of DROP INDEX because such index is associated with a unique constraint (object), so one must drop CONSTRAINT and index will be dropped automatically.

As best practice, If uniqueness is required then always apply unique constraint, instead of creating only unique index. This way you can't drop a unique index accidentally.Also this way you can document all applied constraints easily.
http://technet.microsoft.com/en-us/library/aa224827(v=sql.80).aspx

2
  • @@aasim so unique constraint is better than just creating unique index?
    – AA.SC
    Commented Nov 6, 2014 at 9:32
  • Personally I prefer unique constraint when uniqueness is required. Commented Nov 6, 2014 at 10:20
3

In addition to aasims' answer, the only major difference here being a Unique Index is not a database object. Whereas a unique Constraint is an object.

However other than that there are no other major differences:

  • They both achieve the same performance
  • They both achieve the same end goal.

EDIT:

Why would you use Unique Index vs Constraint, what's the best practice?

There isn't a one size fits all best practice here. For one main reason (at least). A Unique Constraint only allows for one null value in the column. However, you can create a Unique Index without including the null values:

Create unique Index MyUniqueIndex on Contacts (ContactId) where Email is not null

This would create the index but ONLY where the values on the Email column aren't null.

2
  • Actually my curiosity is, if both have same goals, same performance then why we create unique index? So we should always be recommended to use unique constraint, are we?
    – AA.SC
    Commented Nov 6, 2014 at 11:26
  • Edited my answer
    – KidCode
    Commented Nov 6, 2014 at 12:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.