Indexes are a central performance feature of any database, but, it's important to create the right kind of index. Otherwise, instead of speeding up your operations, the wrong index can bring your queries to a halt. Two characteristics ultimately determine which is the correct index: your database's architecture and the business rules or logic your DBMS uses.
You can use the SQL Server Profiler to figure out the types of queries you will use, and then run the Index Tuning Wizard to suggest which columns to use to create a specific index. However, there are some commonsense rules that you can apply that will help you decide what type of clustered or non-clustered index is appropriate. For example:
- Almost all tables should have a primary key that is indexed, particularly if that key is frequently queried.
- Small tables with less than 100 rows don't need indexes. The process of indexing a small table is fast enough that you gain more speed by having the lower overhead of not storing an index.
- It's worth indexing columns that are used commonly in queries, and not indexing any column that isn't.
- Any columns used in an aggregate function (sums, averages, etc.), that contains a GROUP BY or ORDER BY clause, and is used in JOIN queries, are candidates for an index. That's because the database engine uses an index for these operations.
- It's a good idea not to index a column in which the data is constantly changing. Doing so will place a re-indexing
- burden on your database that you want to avoid.
- Also, avoid indexing columns that have data with long data strings
- But do index any column that has a large number of unique values, or any column that is used by multiple queries.
Some situations require indexes. For example, any primary key builds a clustered index. When you use a UNIQUE attribute SQL Server also requires the use of an index to support that function. You should build non-clustered indexes for foreign keys, and for columns that either use aggregate functions, the WHERE clause, or when the results of querying that column yields a small number of rows.
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