SQL Server Interview: What is the difference between Unique Constraint and Unique Index?

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

I am sharing this interview question for Database Developer who has around 1 or 2 years of experience.

Many times, I have asked the difference between Unique Constraint and Unique Index but most of the time people answers were like “No difference – Both are same”. This is a wrong answer.

Which one is the better:

When you are creating a column using UNIQUE constraint or UNIQUE Index, in both the cases SQL Server by default creates a unique non-clustered index.
There is no any performance difference between these two, so you can use any one of this.
But still, there are few minor differences between Unique Constraint and Unique Index.

Disable:

Unique Constraint: You cannot disable the Unique Constraint, you will get below error:
Msg 4916, Level 16, State 0, Line 1
Could not enable or disable the constraint. See previous errors.

Unique Index: You can disable it

1	ALTER INDEX Index_name ON Table_name DISABLE

Errors: The error messages for both violations are also different

Unique Constraint violation error:

1 2 3

Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'uk_tb'. Cannot insert duplicate key in object 'dbo.mytab'. The duplicate key value is (1). The statement has been terminated.

Unique Index violation error:

1 2 3

Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.col1' with unique index 'id_tb'. The duplicate key value is (1). The statement has been terminated.

Filters:

Unique Constraint: You cannot add filter in Unique Constraint.
Unique Index: You can add filter in Unique Index:

1 2	CREATE UNIQUE NONCLUSTERED INDEX idx_name ON dbo.table_name(column_name) WHERE column_name ='M'