Posted by Dan Guzman on 08/08/07 04:05

> My questions is, should I
> create one index contains col1, col2, and col3, or create 3 seperated
> columns. I.e. each column has its own index.

The short answer is that "it depends". To be useful and used efficiently,
indexes must be selective and the high-order column specified in WHERE or
JOIN clauses. Multi-column indexes are especially useful in cases where the
high-order columns are specified in the query and can also cover the entire
query when no other columns are needed. Single column indexes are
appropriate when you have a wide variety of queries.

You might consider trying the Database Tuning Advisor (or Index Tuning
Wizard in pre-SQL 2005 versions). Although experienced DBAs will take the
recommendations with a grain of salt, those tools are a good starting point.
For optimum performance, scrutinize the execution plans of your most
critical queries to ensure adequate indexes are in place. Choose the
clustered index wisely.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sonny" <SonnyKMI@gmail.com> wrote in message
news:1186510203.040216.8690@o61g2000hsh.googlegroups.com...
> Hi,
>
> Would like to know the performance differenece between Multi-column
> Index vs Single Column Indexes. Let's say I have a table with col1,
> col2, col3 along with a primary key column and non-indexed columns.
> In queries, I will use col1, col2, and col3 together and some times
> just one or two of these three columns. My questions is, should I
> create one index contains col1, col2, and col3, or create 3 seperated
> columns. I.e. each column has its own index. Any performance
> difference?
>
> Thanks a lot.
>

[Back to original message]