Unique Clustered index?

Question

If I make a clusted unique index on a column which later starts to hold Non-unique data what would happen?

---

Mr Shaw

Answer 1

Clustered unique index will not allow you to insert duplicate data.

If you want to allow duplicate data in future then drop current index and then recreate Cluster index without selecting unique checkbox.

Answer 2

It will throw an error and prevents entering the non unique data into that column.

Msg 2601, Level 14, State 1, Line 2
Cannot insert duplicate key row in object 'dbo.tablename' with unique index 'ix_indexname'.
The statement has been terminated.

---

Ranjith | My Blog

Answer 3

Why is it that if I mark the index as unique the performance will increase?

Surley if the column is unique but I don't class the index as unique the performance outcome should be the same?

---

Mr Shaw

Answer 4

The unique index on the column is highly selective i.e. for any key search there would be only one match to be returned. So SQL Server does the row selection pretty efficient.

>>If the column is unique but I don't class the index as unique the performance outcome should be the same?

Yes. There would definitely be no noticeable difference.

 

---

Ranjith | My Blog

Answer 5

If I made a mistake and applied the wrong index type could it effect my results row count?

---

Mr Shaw

Answer 6

No!!! what ever is the index type you select it will not effect the results row count for the matching key.

If duplicate Key values are there in the index column SQL Server puts in some extra effort to get those duplicate records as well for the matching key.

 

---

Ranjith | My Blog