You can create a UNIQUE CLUSTERED INDEX
only on a column (or combination of columns) that contains no duplicate data. The fact that you create the index with the UNIQUE
keyword means that you are now constraining users from adding non unique data. (in the key column(s) of the clustered index)
A CLUSTERED INDEX
can be created on a column (or combination of columns) that contains duplicate data. So in this case, the clustered index is not constraining you from adding non unique key data.
However, since a clustered index key is the row identifier, it needs to be able to uniquely identify a row, even if it's created on a column containing duplicate entries. Because of this, if you create a CLUSTERED INDEX
for every row that is a duplicate, SQL Server adds a 4 byte uniquifier to the row, so that the index in fact becomes unique. This is all done under the hood.