In this article I am trying to explain where to use clustered and where not. I am trying to mention some facts that MS told us to focus on.
Clustered indexes are ideal for queries that select by a range of values
or where you need sorted results. This is because the data is already presorted
in the index for you. Examples of this include when you are using BETWEEN,
<, >, GROUP BY, ORDER BY, and aggregates such as MAX, MIN, and COUNT in your
Clustered indexes are good for queries that look up a record with a unique
value (such as an employee number) and when you need to retrieve most or
all of the data in the record. This is because the query is covered by the
Clustered indexes are good for queries that use the JOIN or GROUP BY clauses.
Clustered indexes are good for queries where you want to return a lot of
columns. This is because the data is in the index and does
not have to be looked up elsewhere.
NON Clustered indexes
Avoid putting a clustered index on columns that increment, such as an identity,
date, or similarly incrementing columns, if your table is subject to a high
level of INSERTS. Since clustered indexes force the data to be physically
ordered, a clustered index on an incrementing column forces new data to be
inserted at the same page in the table, creating a table hot spot, which
can create disk I/O bottlenecks. Ideally, find another column or columns
to become your clustered index.
Hope that the information is informative and thanking you to provide your valuable time on it.
Posted by: MR. JOYDEEP DAS