If I have a SQL Server fact table with four dimensions (OrderDate, Customer, Product, Region), my understanding is that it’s best to create a non-clustered index per foreign key (dim key column in the fact table).
Assuming that is correct, is it optimal to combine OrderDate with each dimension in each of the non-clustered indexes as follows – because date is almost always included in a fact table query?
- NC index 1: (OrderDate, Customer)
- NC index 2: (OrderDate, Product)
- NC index 3: (OrderDate, Region)