Indexing - Useful tips

Consider using a clustered index in the following situations:

• The physical ordering supports the range retrievals of important queries—that is, queries that use BETWEEN and LIKE.
• Few duplicate values mean that an equality test (=) returns few rows.
• Many duplicate values mean that an equality test (=) returns many rows.
• The clustered index key is used in the ORDER BY clause of critical queries.
• The clustered index supports the GROUP BY clause of critical queries.
• For a given row in the outer table of a join, there are few rows that match in the inner table. A clustered index on the join column in the inner table will be beneficial.
• For a given row in the outer table of a join, there are many rows that match in the inner table. A clustered index on the join column in the inner table will be beneficial.

When to avoid using a clustered index:

• On a volatile column. A volatile column is a column that is updated frequently. This would result in the data row moving around the table repeatedly.

Some general guidelines are as follows:

• Do not create an index on a column that is not very selective. An example of this would be a column that contained a status flag containing two or three values. It is unlikely that such an index would be used by the query optimizer.
• Be careful when creating indexes with large keys. Fewer keys can be held in an index page, resulting in many index pages and deeper indexes. Take care with a large key in a clustered index. This will be used as the pointer in all the non-clustered indexes on the table.
• Regularly check the levels of internal and external page fragmentation with DBCC SHOWCONTIG. Tidy up by rebuilding indexes. Make sure that there is enough free space in the database to rebuild clustered indexes. Another approach is to use the Database Maintenance Wizard.
• Consider using DBCC INDEXDEFRAG on tables where there is little opportunity for maintenance—for example, a 24 × 7 system.

Consider using a non-clustered index in the following situations:

• Few duplicate values mean that an equality test (=) returns few rows.
• The non-clustered index key is used in the ORDER BY clause of critical queries.
• The non-clustered index supports the GROUP BY clause of critical queries.
• For a given row in the outer table of a join, there are few rows that match in the inner table. A clustered index on the join column in the inner table will be beneficial.
• A critical query can be efficiently covered.
• Many applications will require the selection of a row by the primary key. This is a single-row selection and therefore would normally benefit from the creation of an index containing the same columns as the primary key. Since it is not common to request ranges of primary keys, a non-clustered index is probably the best option. If a primary key constraint is created, the index will be automatically created; it is recommended that this be a non-clustered index.

Avoid using a non-clustered index:

•  When a query returns many rows, such as a range retrieval, or when there are many duplicate values returned by an equality test. Also, if, for a given row in the outer table of a join, there are many rows that match in the inner table, a non-clustered index on the join column in the inner table will not be beneficial.
• Avoid using a non-clustered index on a volatile column. The result may not be as unfavorable as using a clustered index, since the data row will not move; however, the index will still have to be maintained