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

0
votes

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

1
sql-serverindexing
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
Yes, sure, will edit now – Nightman
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

1 Answers

0
votes

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.