CXO

Index on multiple columns for SQL performance

SQL Server allows 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, but find out what you need to do to increase SQL performance if you have more than one column.

SQL Server allows only one clustered index per table because a clustered index reorders the table, arranging the data according to the index key. This results in wonderful performance—when you only have to worry about one particular column.

But what if you want to order the data by more than one column? You can't use a clustered index, but you can create an unclustered index on multiple columns and gain a nice performance increase.

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 the Insert, Delete, and Update operations, so you should use it sparingly. 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.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox