A unique index ensures that the values in the index key columns are unique. A unique constraint guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).
Questions:
Can duplicate values be inserted if we have a unique index on a column and no unique constraint?
Generally, duplicate values cannot be inserted and an error is raised when a unique index exists on the column. The exceptions are:
Index was created with the IGNORE_DUP_KEY option. No error is raised and the insert is ignored.
The non-clustered index is filtered such that the duplicate value does not satisfy the index WHERE clause. The row is inserted but not reflected in the non-clustered index.
What about existing duplicates in any on the column - will it allow to create unique index or unique constraint?
No, with the exception of the filtered index mentioned above.