Create aligned index on a foreign key column

Question

I have a fact table which is partitioned along the PeriodDate column.

CREATE TABLE MyFactTable
(
    PeriodDate DATE,
    OrderID INT 
         CONSTRAINT MyFk FOREIGN KEY (OrderID) REFERENCES DimOrder(ID)
)

I'd like create a partition aligned index on the OrderID column, and as I understood from BOL, I need to include the partitioning key (PeriodDate) in order to have the index aligned.

Like this:

CREATE NONCLUSTERED INDEX MyAlignedOrderIdIndex 
ON MyFactTable (OrderID, PeriodDate);

My question is: in what order should I put the two columns in the index above?

1. ON MyFactTable (OrderID, PeriodDate);

or

2. ON MyFactTable (PeriodDate, OrderID);

As I read on BOL as well the order matters in composite indexes, and my queries will usually use OrderID to lookup Dim table data.

First OrderID, PeriodDate order seems logical choice, but since I am not familiar with partitioning I don't know how it will "like it" when the tables has millions of rows.

What does the best practices dictates here?

Answer 1

My question is: In what order should I put the two columns in the index above?:

(OrderID,PeriodDate) The index is there to enable retrieval of all the facts for a given set of OrderIDs, and if your partitions have multiple PeriodDate's in them, having the index with PeriodDate first, wouldn't be as helpful.

The general rule of thumb here is that you don't partition by the leading column. That way you get partition elimination and the index order as fully independent access paths.

My dimension will have a dozen or maximum a hundred rows. The fact table will have millions of rows. Does it worth to create this index?

You'll have to test. It really depends on the queries. However if your fact table is a clustered columnstore (which a fact table with millions of rows typically should be), you'll probably find that the index is not used much for the query workload. Ie it may be used for queries for a single product, but not for queries that filter by non-key product attributes.