I have a fact table called FactAccount
which has the following columns:
AccountId
(identity)AccountMonthId
- FK referring toDimMonth
AccountYear
The DBA has done the partitioning on the AccountYear
(2014, 2015, 2016...) column. But this table will be queried on the AccountMonthId
column (with a join to DimMonth
) where users will select the year and month from the DimMonth
dimension table.
So in this case, I don't think the partitioning will make any difference as the WHERE
clause doesn't filter by AccountYear
.
The DimMonth
dimension contains all combinations of year and month, i.e. 12 dimension members for each year and the AccountMonthId
column is an identity column, not a natural key.
Is there a way to partition a table on a column in a table referenced by a foreign key? Or, to be very specific in my case, is it possible to partition the FactAccount
table on DimMonth.AccountMonthId
?
DimMonth
contain one row per month (total of 12 rows) or one per (Year, Month)?