Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I got difference between clustered index and unique index here. But what is the exact difference between clustered index and unique-clustered index?
AFAIK creating clustered index can be done for a column having unique values and if value is duplicate then clustered index cannot be set. Same is the case with unique-clustered index.
So i want to know the difference between them.
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.
