CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008indexing
Dan P
Dan P

Reputation: 1999

SQL Server 2008 Index Multiple Columns Some not used all the time

I have a table with columns 

ParameterValueId, SiteId, LocationId, ParameterId, SampleDateTime

and a few other columns.

ParameterValueId is the primary key.

I want to create an index that is not unique to speed up queries on SiteId, LocationId, ParameterId, SampleDateTime.

Can I just create one index on SiteId, LocationId, ParameterId, SampleDateTime?

Or do I need to create 4 indexes?

I guess my question is if I create an index on 4 columns will it be used and still improve performance if I only use 1, 2, or 3 of those columns and not all 4?

Upvotes: 1

Views: 328

Answers (1)

Stuart Ainsworth
Stuart Ainsworth

Reputation: 12940

As a general rule-of-thumb, given the scenario you've described, one index on all four columns is probably a good starting point. However, there may be other things you need to do to improve performance depending on the nature and shape of your data (for example, how disparate is SiteID? Is it unique? Does a single value account for more than 20% of the values in that colum?).

Short Answer: one covering index will be used, even if not all of the covered columns are used in the query.

Upvotes: 6

Related Questions