SQL Server - Unique index vs Unique constraint - Re. Duplicate values

Question

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:

1. Can duplicate values be inserted if we have a unique index on a column and no unique constraint?
2. What about existing duplicates in any on the column - will it allow to create unique index or unique constraint?

Answer 1

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:

1. Index was created with the IGNORE_DUP_KEY option. No error is raised and the insert is ignored.

2. 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.

Answer 2

One potentially unintuitive scenario that confused me at first: postgres does not treat NULL values as equal. If your table looked like this:

+-------+-------+-------+
|id     |a      |b      |
+-------+-------+-------+
|1      |0      |NULL   |
|2      |0      |NULL   |
+-------+-------+-------+

You could still add a unique index on columns a and b. According to Postgres, row with id 1 and row with id 2 have the same value for column a, but different values for column b