Difference between cluster and non clustter index

Question

Hi everyone,

Wanted to know about difference between cluster and non cluster index with example.

when to use cluster index and non cluster index .

links will also help.

regards,

ajay

Answer 1

I think you would understand better with video

refer this https://www.youtube.com/watch?v=ITcOiLSfVJQ

and some articles

http://www.codeproject.com/Articles/173275/Clustered-and-Non-Clustered-Index-in-SQL

---

Hope it Helps!!

Answer 2

Hi everyone,

Wanted to know about difference between cluster and non cluster index with example.

when to use cluster index and non cluster index .

Ajay its easy to find answer to these types of one linr question online. Develop the habit to help yourself you would find this habit  will help you in more learning.

There is no clear demarcation when to use CI and NCI and neither you can learn from definitions. Purpose of index is to make query faster if it does keep it f it does not remove it.

Read books online about CI and NCI indexes

---

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My Technet Wiki Article
MVP

Answer 3

A Shanky says, there is a ton of material out there if you go searching, so I will make it quick.

A clustered index has the data pages at the leaf of the index, and therefore there can only be one clustered index on a table. In SQL Server it is highly recommendable to always have a clustered index, and if you no more index that would be the primary key. Note that in other products, clustered indexes are seen as a fairly strange creature that you only use in special situations.

If you have multiple indexes on a table, you would choose the clustered indexes from these criterias (which are to some extent conflicting):

* Montonically incrementing to reduce page splits.
* Columns that are often used in range queries.
* Short, since the clustered index key is included as the row locator in a non-clustered index.

---

Erland Sommarskog, SQL Server MVP, [e-mail]