SQL Server permits only one clustered index per table, because a clustered index reorders the table, arranging the data according to the index key. This is great if you only have one column. What can you do to increase SQL performance if your table has more than one column?

You can gain a nice query performance improvement by creating an unclustered index on multiple columns.

For instance, say you need to visit your Sales table, ordered by SalesRep and Date. You could create two indexes and leave it up to the Query Optimizer to figure out what you need, or you could create an index on both columns. SQL Server allows up to 16 columns in an unclustered index.

Every unclustered index contains the key values you specify, plus the value obtained from the clustered index; this acts as a pointer to the actual rows when you need their data. This means that the query engine will never need to visit the actual rows—provided that your unclustered index is keyed on the values you need for a given task. It will learn everything it needs from the index and scope, and then it will visit the qualifying rows.

This technique is no magic bullet. Every additional index adds overhead to Insert, Delete, and Update operations. However, it's worth your time to examine the most popular queries and reports in your application. It's likely that a few well-considered compound indexes will deliver a nice increase in performance, and that these indexes will affect more than one query.

The nice thing about this approach is that you can always undo it. If the performance hit is unacceptable, simply delete the index.