What is difference between is_unique column and is_unique_constraint columns in sys.indexes

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

• Create statement for both types of indexes are same except fill factor property value or do I missing something?

• If statement 1 is true then why we have two columns in sys.indexes for unique constraint?

-
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?

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

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

• 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).

• 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.

• Unique constraint is maintained through a unique index

• Just like Default and Primary Key, a unique constraint can't be disabled using NOCHECK syntax

• 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

StackExchange Database Administrators Q#82035, answer score: 4