Tuesday, 24 July 2012

Scenario based Index type selection


When we are thinking about index we have different options in mid to use them, such as Clustered / Non Clustered etc.
It is very important to understand in which scenario what type of Index gives us the performance boost up. As we all know that the too many indexes are not good for performance.
In this article I am trying to demonstrate the Scenario wise index selection process.   
Scenario with Index Type
Here I am trying to create a table type structure, in one side I use the type of index and in opposite side I am using the scenario related to it.

Clustered Index
Clustered Index used in columns in the Query
1.    The query that returns the range of values by using operators such as BETWEEN clause, >, >=, < and <= etc.
2.    When the Query returns the larger result set.
3.    When using JOIN clauses with FOREIGN key columns.
4.    When ORDER BY or GROUP BY clauses in the query.

Use the Clustered Index on the following columns of Table

1.    If the columns have the UNIQUE values.
2.    The columns that are frequently used to retrieve the range of values.
3.    The IDENTITY columns that are guaranteed to be UNIQUE and auto increasing numbers.
4.    Columns that are used to sort data by using ORDER BY clause in SELECT statements.

Clustered Index is NOT good for the following columns of Table

1.    The Columns those are frequently modified or changed.
2.    Columns that use the composite key.
Non Clustered Index
Non Clustered Index used in columns in the Query
1.    Query that use the JOIN or GROUP BY Clause.
2.    Query that does not return large result set or data.
3.    The columns of the query where we frequently use the WHERE clauses.

Use the non Clustered Index on the following columns of Table

1.    Non clustered index is used in the non key columns that are frequently used in the query set.
2.    In the columns where data is not distinct such as combination of columns where the clustered index is used in the others columns.

Covering Non Clustered Index

If we have to use a non-clustered index and if we know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table for the query

Guideline to create Covered Index

·         If the query or queries we run using the covering index are  
      seldom run, then the overhead of the covering index may  
      outweigh the benefits it provides.

·         The covering index should not add significantly to the size of 
      the key. If it does, then it its use may outweigh the benefits it 

·         The covering index must include all columns found in the 
      SELECT list, the JOIN clause, and the WHERE clause.

Filtered Non Clustered Index
Use the filtered index in the following scenario

1.    There is a table that has huge amount of data and we often select only a subset of that data.  For an example, we have a lot of NULL values in a column and we want to retrieve records with only non-NULL values. In MS SQL Server 2008 this type of columns is called Sparse Column.

2.    In another scenario we have several categories of data in a particular column, but we often retrieve data only for a particular category value.

By this I mean to say that in the Query of Indexed column the WHERE statement is more or less fixed and the same WHERE statement is used in non clustered Filtered Index.

Related tropics

Hope you like it.

Posted by: MR. JOYDEEP DAS


  1. Thanks "Joydeep"
    It's a very good article. I read all of your article related to index... Very good. If you provide some performance related differences between them it will be a grate help.

  2. It's really good article. I read several article related to Index but not found proper implementation guideline.
    Once again thanks for your it.