Few points you should consider while creating Index


The purpose of an index in SQL Server is to allow the server to retrieve requested data, in as few I/O operations as possible, in order to improve performance. To make right indexing choice, you might get the n number of recommendations or best practices document on the web. While I would recommend going on below link the video by Kimberly Tripp on Index creation with SQL Server 2005 (Note this video also applies to SQL Server 2008)

http://www.microsoft.com/emea/spotlight/sessionh.aspx?videoid=29

Hints that you should keep in mind while creating Clustered index:

  • Columns that contain a large number of distinct values.
  • Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
  • Columns that are accessed sequentially.
  • Queries that return large result sets.
  • Columns that are frequently accessed by queries which involves JOINs or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates
    the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
  • OLTP-type applications where very fast single row lookup is required, typically by means of the primary key.
    Create a clustered index on the primary key (this is the default state of SQL Server).

Hints that you should keep in mind while creating Non-Clustered index:

  • Columns that contain a large number of distinct values, such as a combination of last name and first name (if a clustered index is used for other columns). If there are very few distinct values, such as only 1 and 0, most queries will not use the index because a table scan is usually more efficient.
  • Queries that do not return large result sets
  • Columns frequently involved in search conditions of a query (WHERE clause) that return exact matches.
  • Decision-support-system applications for which joins and grouping are frequently required.
    Create multiple non-clustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns.
  • Covering all columns from one table in a given query. This eliminates accessing the table or clustered index altogether.
Advertisements

One thought on “Few points you should consider while creating Index

  1. Valuable information. Fortunate me I found your site unintentionally, and I am surprised why this twist of fate did not happened in advance!
    I bookmarked it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s