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.

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 (AT) gmail (DOT) com> wrote

On Aug 7, 1:10 pm, Sonny <Sonny... (AT) gmail (DOT) com> wrote:
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/