suhasini.m (4/8/2009)

---

When i am creating a new table how to identify whether i should create a Clustered or Nonclustered Index on this table? In terms of performance which one is better?

geramkumar (4/9/2009)

---

* no need create index on tables having little records or columns having duplicates (like country, gender etc).

* avoid duplicate index sets like (col1,col2, col3) and (col1, col3,col2)

Ramkumar (LivingForSQLServer) (4/9/2009)

---

best practices are

* frequently used tables must have a clustered index
* no need create index on tables having little records or columns having duplicates (like country, gender etc).
* avoid duplicate index sets like (col1,col2, col3) and (col1, col3,col2)
* create indexes on columns which are frequently used in WHERE and JOINS.

guidelines for selecting clustered indexe key
* columns that contain large number of unique values
* queries that returns range of values.
* columns that are accessed sequentially
* queries that returns large resultset
* columns which are not updated frequently.

guidelines for selecting nonclustered indexe key

* queries dont return large resultset.
* columns frequently involved in WHERE having exact match