SQL Server unique index allowing duplicates

I am using SQL Server 2008, had a table with an id (numeric) column as the primary key. Also had a unique index on three varchar columns. I was able to add a row with the exact same set of the three columns. I verified it with a simple query on the values and 2 rows were returned.

I edited the index and adde开发者_运维知识库d the id column. When I tried to edit it again and remove the id column it complained that there were duplicate rows, it deleted the index but couldn't create it.

I then clean the database of the duplicated, recreated the index with the same 3 varchars as unique and nonclustered and now it works properly, not allowing duplicates.

Does anyone know why the uniqueness of this index was ignored?

The index could had been disabled (see Disabling Indexes), your 'duplicate' values may had been different (trailing spaces for example), or your test may be incorrect.

For sure you did not insert a duplicate in a enforced unique index.

I'm not a pro on this subject, but the 'is unique'-setting of the index probably refers to the way the index is build/stored, not that it is a constraint. This probably also means the performance of the index is sub-optimal. When creating the index, the DBMS might check this.