Problem solve Get help with specific problems with your technologies, process and projects.

Guidelines for creating indexes

Some guidelines for index usage to maximize database performance.

Indexes greatly speed up operations such as queries and are an essential design feature for joins. However, indexing is not free. Every index you create adds overhead to your database. A well performing database therefore minimizes index creation while emphasizing the creation of the "right" mix of indexing. Here are some rules to guide you in your table designs when you are trying to decide what to index.

  • Columns used to join your tables (essential for DB function)
  • Columns used in range queries
  • Columns used in any query that has a GROUP BY or ORDER BY clause
  • Any column that is required to generate an important aggregate function such as SUMs, AVERAGEs, etc.

If your database is performing poorly, don't forget to look at the indexes that already exist. If there are non-essential indexes that are very rarely used but are constantly maintained, get rid of them. If your database is missing an index which could significantly improve an important function like a query, add that index.

Consider removing the following index types:

  • Indexes found in tables with a limited number of rows
  • Tables that change very quickly but aren't used in evaluations. These tables typically are altered in INSERT or DELETE operations.
  • Columns that are large in contents or that have a very wide range of values.
  • Any column that isn't used to generate a query.

Since each table can have a single clustered index, you want to create a clustered index when you encounter the same conditions as you do for a regular index. That is, columns used in range queries, queries with an ORDER BY or GROUP BY clause, queries that return bid data sets, and as an index of a table join.

Use non-clustered indexes when you have columns that need a foreign key in a table, that are part of an aggregate function, or when a query gives you a small number of returned rows. You'll also find non-clustered indexes useful when you have keys that are identity columns or sequential numbers; that is primary keys that are surrogate keys.

One advantage that non-clustered indexes have over clustered ones is that when you issue a DBREINDEX command, your non-clustered index can be rebuilt without having to rebuild the clustered index. If you rebuild a clustered index, SQL Server rebuilds all of your non-clustered indexes as part of the same operation.

Barrie Sosinsky is president of consulting company Sosinsky and Associates (Medfield MA). He has written extensively on a variety of computer topics. His company specializes in custom software (database and Web related), training and technical documentation.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning