In reply to: "I would also like to see some examples of when to use clustered index and when to use non-clustered index if possible."

Start with the behavior of the two different index types and the answer starts to become clear.

1. The "natural order" of records in a table is the physical order of the records in the table as they would be scanned from first to last without an index or sort order.

2. A "clustered index" physically reorganizes every record in the table such that the "natural order" of the records in the table becomes the logical sort order of the clustered index. A seperate index records at which record the selected column value(s) change. This is why there can be only one "clustered index" per table.

3. A "non-clustered index" leaves the records as they are in the table and creates a seperate lookup tree of only the column(s) for which the index is built. Each node of this tree points to the location in the table of the corresponding record value.

In short:

1. Use a "clustered index" only on values that are highly repetetive in a table and for which you often select sequential ranges of data. The "clustered index" will then very quickly return entire blocks of matching records.

2. Use a regular index on values that are generally distinct and for which you often return individual records.

Rule of Thumb: If you seek values from a table based on a WHERE clause of a single column, be sure to have an index on it. If the values of the column are highly repetitive, use a clustered index. A column that will have a distinct value for every record is a poor candidate for a clustered index.