What type of Index You Choose for Primary Key?

 

In one of my article, someone wants to know more about where we used the clustered index and where to non clustered index. First of all I am thanking you "Anonymous" for giving your valuable time to it.

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

Point-1

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
queries.

Point-2

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
index.

Point-3

Clustered indexes are good for queries that use the JOIN or GROUP BY clauses.

Point-4

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