We get a lot of index questions related to what type of index should be created given a particular situation. I compiled the items below from books-online to facilitate this discussion:
Before creating clustered indexes, understand how your data will be accessed.
Consider using a clustered index for: * Columns that contain a limited number of distinct values, such as a state column that contains only 50 distinct state codes. However, if there are very few distinct values, such as only 1 and 0, no index should be created. * 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 involving join 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.
Clustered indexes are not a good choice for: * Columns that undergo frequent changes because this results in the entire row moving (because SQL Server must keep the rowÆs data values in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile. * Covered queries. The more columns within the search key, the greater the chance for the data in the indexed column to change, resulting in additional I/O.
Before creating nonclustered indexes, understand how your data will be accessed.
Consider using nonclustered indexes for: * Columns that contain a high 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, no index should be created. * 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 nonclustered indexes on columns involved in join and grouping operations, and a clustered index on any foreign key columns. * Covered queries.