Tip

Guidelines for creating indexes

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

    Requires Free Membership to View

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.


This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.