Partitioning using column from different table

Question

I have a table ActionValues with the following columns:

1. AccountID
2. Action
3. ActionValue

This table is related with a second table called Actions that has the following columns:

1. AccountID
2. Action
3. ActionDateTime

I would like to partition the ActionValues with a partition scheme that allows me to maintain 12 partitions (1 for each month of the year). Every month, the oldest partition would be truncated/merged and a new one would be added. To do this, I need to create a clustered index on a column from the original ActionValues table. However, I can only get this information from the Actions with the AccountID/Action relation.

Would it be possible to create a clustered index on the ActionValues table using the ActionDateTime column? Do I need to create an extra column in ActionValues to achieve this?

Answer 1

Would it be possible to create a clustered index on the ActionValues table using the ActionDateTime column?

Nope

Do I need to create an extra column in ActionValues to achieve this?

Yes

From https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver16#partitioning-column your partitioning column must be regular column so you should de-normalize your table.

Please note column ActionValues do not have to be the first of your clustered index.