0

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?

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.