SQL Server– Unique Constraint VS Unique Index

Both  enforces uniqueness of the column. When we define an Unique constraint, SQL server creates an unique index (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 onward, 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 them.


About Sandip Pani

Sandip Pani is a Database/BI developer and speaker and is located in Bangalore, India. He has been working with SQL Server over 11 years. He is MCITP for SQL Server 2008 and specializes in SQL Server for application developers and performance and query tuning. His specialization is into Query Tuning and performance troubleshooting. He is working as Senior Technical Specilist for NextGen Healthcare. He is active in SQL community world. He share and enhance his knowledge of SQL Server by spending time at newsgroups and forums, reading and writing blogs, and attending and speaking at conferences.

View all posts by Sandip Pani →

Leave a Reply

Your email address will not be published.