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