Scenario based Index type selection

Introduction

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.

Type	Scenario
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        provides. ·         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

1.    Related to Clustered Index
http://sqlknowledgebank.blogspot.in/2012/07/related-to-clustered-index.html

2.    Index Fragmentation
http://sqlknowledgebank.blogspot.in/2012/07/index-fragmentation.html

3.    Index defragmentation
http://sqlknowledgebank.blogspot.in/2012/07/index-defragmentation.html

4.    Index and Disk space
http://sqlknowledgebank.blogspot.in/2012/07/index-and-disk-space.html

5.    Filtered non Clustered Index
http://sqlknowledgebank.blogspot.in/2012/07/filtered-non-clustered-index.html

6.    Index and Sort Order
http://sqlknowledgebank.blogspot.in/2012/07/index-and-sort-order.html

Hope you like it.

Posted by: MR. JOYDEEP DAS