Tips on Optimizing SQL Server Composite Indexes

A composite index is an index that is made up of more than one column. In some cases, a composite index is also a covering index. Generally speaking, composite indexes (with the exception of covering indexes) should be avoided. This is because composite indexes tend to be wide, which means that the index will be larger, requiring more disk I/O to read it, hurting performance.

The main reason composite indexes (not covering composite indexes) are used is to make an index more selective. A better way to ensure selectivity is good database design, not creating composite indexes. [6.5, 7.0, 2000, 2005] Updated 10-16-2006

*****

If you have no choice but to use a composite index, keep the "width" of it as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance. [6.5, 7.0, 2000, 2005] Updated 10-16-2006

*****

A composite index is generally only useful to a query if the WHERE clause of the query matches the column(s) that are leftmost in the index. So if you create a composite index, such as "City, State", then a query such as "WHERE City = 'Springfield'" will use the index, but the query "WHERE STATE = 'MO'" will not use the index. [6.5, 7.0, 2000, 2005] Updated 10-16-2006

*****

Even if the WHERE clause in a query does not specify the first column of an available index (which normally disqualifies the index from being used), if the index is a composite index and contains all of the columns referenced in the query, the query optimizer can still use the index, because the index is a covering index. [6.5, 7.0, 2000, 2005] Updated 10-02-2006

*****

When you create an index with a composite key, the order of the columns of the key is important. Try to order the columns in the key to enhance selectivity, with the most selective columns to the leftmost of the key. If you don't do this, and put a non-selective column as the first part of the key, you risk having the Query Optimizer not use the index at all. Generally, a column should be at least 95% unique in order for it to be considered selective. [6.5, 7.0, 2000] Updated 10-02-2006

*****

Sometimes, it is a good idea to split a composite index into multiple single-column indexes. This is because only the first column in a composite index has statistics stored for it. And if this first column is not very selective, it may not be used by the Query Optimizer. But if you were to break up the composite index into multiple indexes, then statistics will be kept for each column, and the Query Optimizer will have better information to make better decisions on how to use indexes. SQL Server has the ability to join two or more individual indexes and intersect them, just as if you were using a composite index, giving you the best benefits of single and composite indexes.

This is not to say that multiple single indexes are always better than a single composite index. Only through testing will you know for sure which strategy will offer the best performance in your particular circumstance. [7.0, 2000, 2005] Updated 10-02-2006

*****

As you may know, an index is automatically created for column(s) in your table that you specify as a PRIMARY KEY or as UNIQUE. If two or more columns are involved, and a composite index is created, you should choose how the columns will be ordered in the composite index, instead of accepting the default choices offered by SQL Server.

This is because you always want to use the most selective columns at the left of the key to ensure that the composite index is selective enough to be used by the Query Optimizer. If you don't do this, then the default order of the columns in the composite index may not be very selective, and the index may not be used by Query Optimizer. [6.5, 7.0, 2000, 2005] Updated 10-02-2006