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.
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:
123 Msg 2627, Level 14, State 1, Line 1Violation 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:
123 Msg 2601, Level 14, State 1, Line 1Cannot insert duplicate key row in object 'dbo.col1' with unique index 'id_tb'. The duplicate key value is (1).The statement has been terminated.
Unique Constraint: You cannot add filter in Unique Constraint.
Unique Index: You can add filter in Unique Index:
12 CREATE UNIQUE NONCLUSTERED INDEX idx_nameON dbo.table_name(column_name) WHERE column_name ='M'