Composite Index Recommendations

Question

I have a huge (millions of rows) table containing the following columns:

[When] datetime2(0), [What] tinyint, [Who] bigint 

This is basically a table of events (“What”) that happened to various objects (“Who”) at a time (“When”). There are about 10 different meanings for “What” - this is an enumeration. There are currently about 10,000 values ​​for Who.

I want to be able to query this table to ask if something happened to one or more objects between a certain time. For example, [What = 0, 1, 2, 3] happened with [Who = 0, 1, 2, 3] between [When = '2012-10-01' to '2012-11-01'].

I am looking for tips on how best to index this table. I'm sure a composite index is the way to go, but I'm not sure about the exact configuration. For example, what should be the order of the columns? I read that the "most selective" columns should go to the left, and I think that in this case there will be [When], [Who], [What]. Is it correct?

Sorry if this question seems vague, but I would be grateful for any input. I am using SQL Server 2012.

Answer 1

The myth of placing the most selective column on the left - shit - sorry.

Your composite index will only be useful if you use the n leftmost arguments, for example. if you have an index on

 (when, who, what) 

then this index can answer the question about

 (when) 

or about

 (when, who) 

or even around

 (when, who, what) 

but cannot answer questions about

 (who, what) 

(since the leftmost column is not used here).

It should be what you think - order the columns in such a way that you can answer most of your questions with such a composite index.