What is Index?

Indexes are used to change the order of the rows in the table (Clustered Indexes) or to add metadata that points to the rows in the table (Non-Clustered Indexes) for improving the performance of the queries on the table.

Clustered Indexes store the rows in the table in physically sorted order. Whereas, Non-Clustered Indexes store the pointers to the rows in the table. This helps in improving the performance of the queries on the table.

Guidelines for Clustered Index

With few exceptions, always create clustered indexes on columns frequently used in queires.

Use Clustered Index on columns used in following queries:

1. Queries that return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

2. Queries that return large result sets.

3. Queries that use JOIN clauses using Foreign Key columns.

4. Queries that use ORDER BY or GROUP BY clauses.

For example, an index on the columns specified in the ORDER BY or GROUP BY clause may remove the need for the Database Engine to sort the data, because the rows are already sorted. This improves query performance.

Use Clustered Index on following columns in a table:

1. Columns that are unique or contain many distinct values.

For example, an Author ID uniquely identifies authors. A clustered index or PRIMARY KEY constraint on the AuthorID column would improve the performance of queries that search for author information based on the Author ID.

A clustered index could also be created on author's FirstName, MiddleName, LastName because author records are frequently grouped and queried in this way, and the combination of these columns would still provide a high degree of difference.

2. Columns that are frequently used to return a range of values by using operators such as BETWEEN, >, >=, <, and <=.

For example, a customer ID uniquely identifies customers in the Customer table. Queries which consist of sequential search, such as WHERE CustomerID BETWEEN 900 and 999, would benefit from a clustered index on CustomerID. This is because the rows would be stored in sorted order on that key column.

3. Columns that are defined as IDENTITY because these columns are guaranteed to be unique within the table.

4. Columns that are frequently used to sort the data retrieved from a table (using ORDER BY clause).

It can be a good idea to cluster (i.e. physically sort) the table on that column to save the cost of a sort operation every time the column is queried.

Clustered Indexes are NOT good for the following columns in a table:

1. Columns that undergo frequent changes.

This causes the whole row to move which affects the performance, because the Database Engine must keep the data values of a row in physical order. This is an important consideration in large-volume transaction processing systems in which data is typically large and volatile.

2. Columns used in Composite keys.

Guidelines for Non-Clustered Index

Consider creating one or more non-clustered indexes on databases or tables with low update requirements, but large volumes of data to improve query performance.

Please note that, large numbers of indexes on a table affect the performance of INSERT, UPDATE, DELETE, and MERGE statements because all indexes must be adjusted appropriately as data in the table changes. Therefore, avoid creating indexes on this kind of tables.

Use Non-Clustered Index on columns used in following queries:

1. Queries that use JOIN or GROUP BY clauses.

Create one or more non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.

2. Queries that do not return large result sets.

3. Queries that contain frequently involved columns in search conditions, such as WHERE clause, which returns exact match.

Use Non-Clustered Index on following columns in a table:

1. Create non-clustered index on non-key columns that are frequently used in queries.

2. Columns with lots of distinct values, such as a combination of last name and first name, if a clustered index is used for other columns.

Please refer the below links

http://developergeeks.com/article/20/how-to-use-clustered-index-and-non-clustered-index-on-columns-in-sql-server

http://www.sql-server-performance.com/2004/index-data-structures/


Thanks