Unique index vs unique constraint in SQL Server

To start with there is no practical difference between Unique Index and unique constraint; they both accomplish the task of providing uniqueness to a column.

A unique constraint is enforced by creating a unique index under the hood. Let’s create a unique index and unique constraint on a column and analyze the metadata.

USE AdventureWorks2014
GO
CREATE UNIQUE NONCLUSTERED INDEX unique_index_Name ON Person.Addresstype(NAME)
GO
ALTER TABLE Person.AddressType ADD CONSTRAINT unique_constraint_Name UNIQUE(Name)
GO

The above query creates a unique index unique_index_name and a unique constraint unique_constraint_name on Person.Addresstype.Name column. As mentioned above that a unique constraint is enforced by a unique index, let’s analyze indexes on Person.Addresstype table.

The only difference we can see in above image is that constraint index – index_description includes “unique key” keyword, rest all is same.

A unique constraint can’t be disabled like foreign key and check constraint however, the unique index even the one enforced by unique constraint can be disabled as shown below.

ALTER INDEX unique_constraint_Name ON Person.Addresstype DISABLE

Let’s verify whether its disabled or not.

To enable the index execute the below query.

ALTER INDEX unique_constraint_Name ON Person.Addresstype REBUILD

As compared to unique constraints, unique index gives more options in terms of Fill Factor, included columns etc. however, it’s just that unique constraint clear the intentions that it is a constraint where as a unique index doesn’t, this is because the word constraint literally means that value is being checked whereas index is related to increasing performance. It’s up to you to select any of these to enforce unique constraint on a column.

Like us on FaceBook  |  Join the fastest growing SQL Server group on FaceBook