Multi-column Index vs Single Column Indexes

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.

Re: Multi-column Index vs Single Column Indexes

> 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" 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.
>

Re: Multi-column Index vs Single Column Indexes

On Aug 7, 1:10 pm, Sonny wrote:
> 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.

It depends on your workload. You need to try it out using the mix of
reads and writes similar to your actual activity.

Alex Kuznetsov, SQL Server MVP
http://sqlserver-tips.blogspot.com/