We help IT Professionals succeed at work.

Partitioning a SQL table based on a column in another table

Hello Experts,

I have a very large table with more than 100 million records, which is part of a Data Warehouse System on a SQL Server 2012 Enterprise edition.

I need to partition this table based on a date column, but the date column is stored in another table and both tables are related via a meaningless Primary Key.

Is there any way I can do the partitioning based on the Date column in the other table?

FYI, I made a quick search on the internet, and one solution is to add a redundant date column to the table that needs to be partitioned, but I'm not quite thrilled with this option and I hope there is a better way.

Thanks for your help,
Watch Question

Senior DBA
Most Valuable Expert 2018
Distinguished Expert 2019
No other way.  You need to include the date column in your other table as well, denormalizing that date.