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)
Creating indexes is more about use than simply because something is a foreign key. Indexes increase the cost of writing to the table so you need to be somewhat reserved about adding them blindly. You want indexes to be created on the columns that are used in the where/group by/order by/joins. Columns that are commonly returned in the select only are better off being added as INCLUDE, as this gives the index a reference to the value but does not require the index to be rebuilt with changes to that column. If you use all four of those values in conditions then the best solution is probably a single index that has all four columns. Adding indexes is about balancing concerns, they should be added as the result of analysis/testing.