Does single index have benefits when selecting on several columns? SQL Server

Question

For example there is a table with columns: ColumnA, ColumnB, ColumnC, ColumnD

And there are several selects with where clause on:

ColumnA, ColumnB, ColumnC
ColumnA, ColumnC, ColumnD
ColumnA, ColumnB, ColumnD

The goal is to speed up these queries. But I don't want to create multi columns indexes for all columns combinations, because it will take a lot of place and may slow down the system.

Is there a sense to create an index just for ColumnA? Will it improve the queries performance?

Thanks

The question is not if the columns are part of the select but if they are part of a condition in the where clause. — a_horse_with_no_name
The Execution Plan for your queries will show whether, and how, an index is being used. You may be surprised at the choices the query optimizer makes. — HABO

Johnny Bones Johnny Bones · Accepted Answer · 2013-06-20T17:19:12

Indexes are used as a way to reduce "table scanning". You should index whichever column or combination of columns is used most often. If that means using 2 columns, that's fine.

Also remember that a CLUSTERED index is a physical page ordering of the data. NON-CLUSTERED is not. So, any clustered index is going to be much more efficient. You could always make the most used field a clustered index, and put a non-clustered index on a field not used as often.

It's all about the data, in the end. I used to work with healthcare tables that were over 1B records, Indexing is VERY important in an environment like that.

Does single index have benefits when selecting on several columns? SQL Server

1 Answers