Can you please differentiate blw Unique Clustered Index and Non Unique Clustered Index.

Question

Can you please differentiate b/w the following:

Unique Clustered Index
Non Unique Clustered Index
and explain what are these?

Thanks,

Nagaraju K.

Answer 1

Hi
Check below link

http://sqlserverpedia.com/blog/sql-server-bloggers/unique-and-non-unique-non-clustered-indexes-on-a-unique-clustered-index/

Kayden

Answer 2

the difference is that the index key is unique for one and not for the other.  In the case of a non-unique key, sql server has to create a unique key by appending some other values to the key.  This adds additional overhead for both performance and storage.  Additonally SQL Server could pontentially use a less efficient query plan for queries becuase it doesnt know the key used in the predicate is unique.  Always use a unique index where available, as this will help to optimize performance, save storage, and gives you insight into the data without querying the table.

---

http://jahaines.blogspot.com/

Answer 3

UNIQUE CLUSTERED INDEX is typically used for PRIMARY or UNIQUE KEYs.  It is helpful with range searches among others.

NON-UNIQUE CLUSTERED INDEX must have a very limited application role, I never used it.

Related article:

http://www.sqlusa.com/bestpractices2008/allindexes/

Kimberly Tripp on the topic: "

Let's start with the key things that I look for in a clustering key:
* Unique
* Narrow
* Static

 Why Unique? A clustering key should be unique because a clustering key (when one exists) is used as the lookup key from all non-clustered indexes. Take for example an index in the back of a book - if you need to find the data that an index entry points to - that entry (the index entry) must be unique otherwise, which index entry would be the one you're looking for? So, when you create the clustered index - it must be unique. But, SQL Server doesn't require that your clustering key is created on a unique column. You can create it on any column(s) you'd like. Internally, if the clustering key is not unique then SQL Server will “uniquify” it by adding a 4-byte integer to the data. So if the clustered index is created on something which is not unique then not only is there additional overhead at index creation, there's wasted disk space, additional costs on INSERTs and UPDATEs, and in SQL Server 2000, there's an added cost on a clustereD index rebuild (which because of the poor choice for the clustering key is now more likely). "

http://stackoverflow.com/questions/4332982/do-clustered-indexes-have-to-be-unique

---

Kalman Toth SQL SERVER & BI TRAINING