What is the difference between Unique Key and Index with IsUnique=Yes?
Sql ServerDatabaseSql Server Problem Overview
I have a table with a primary key, but I want two other columns to be constrained so the combination of the two is guaranteed always to be unique.
(a dumb example: In a BOOKS table, the IBAN column is the primary key, but the combination of the Title and Author columns should also always be unique.)
In the SQL Server Management Studio it's possible to either create a new Index and set IsUnique to Yes, or I can create a new Unique Key.
What is the difference between the two approaches, and which one suits best for which purposes?
Sql Server Solutions
Solution 1 - Sql Server
Creating a UNIQUE constraint is a clearer statement of the rule. The IsUnique attribute of the index is an implementation detail - how the rule is implemented, not what the rule is. The effect is the same though.
Solution 2 - Sql Server
There is a clear difference between the 2. A unique constraint defines what combination of columns has to be unique. A unique index is just a way of making sure the above is always valid. But it's possible to have a non-unique index supporting a unique constraint. (if the constraint is deferable = Only has to be valid at commit time but is allowed to be broken in the middle of a transaction)
Solution 3 - Sql Server
Just so that you know, when you create a unique constraint SQL Server will create an index behind the scenes
Solution 4 - Sql Server
One thing I just found out the hard way is that in SSMS scripting of unique keys was set to true by default but the scripting of indices was set to False. When I used the Script Table As context menu from SSMS I didn't get my unique indices.
Also if the type is set to Unique Key, you can't change the "Ignore Duplicate Key" setting. First you have change the type from Unique Key to Index then you can set Ignore Duplicate Keys to true.
Solution 5 - Sql Server
unique indexes are unique keys.
Solution 6 - Sql Server
I do not think there is any difference between them but using unique index , we can have two benefits , as the column is already unique and also had the index on it so i gonna be more faster to search . So using unique index is more benefit.