Partitioning Master Detail Table

Question

I Use SQL Server 2008 R2 and want to partitioning Master table and Detail table together. How can I partitioning Detail table by MasterTypeID field in Master table.

My Partition Function is :

CREATE PARTITION FUNCTION MasterTypeFN(int)
AS 
RANGE LEFT FOR VALUES (1,2,3)

My Partition Schema is :

CREATE PARTITION SCHEME MasterTypeScheme 
AS 
PARTITION MasterTypeFN 
TO ([FG1], [FG2], [FG3], [PRIMARY])

My Master Table Structure is :

CREATE TABLE [dbo].Master  
(
   [MasterID] [int] NOT NULL,
   [MasterTypeID] [int] NOT NULL,
   ...
) 
ON MasterTypeScheme (MasterTypeID)

My Detail Table Structure is :

CREATE TABLE [dbo].Detail
(
   [DetailID] [int] NOT NULL,
   [MasterID] [int] NOT NULL,
   ...
) 

I want to Partitioning Detail table with regard to master partition. In other word I want to save Master table record and related details in one filegroup.

Answer 1

You should define MasterTypeID column in Detail table and define permission on it to disable update this column. and create trigger on Master table to sync MasterTypeID column in Master table with MasterTypeID column in Detail table.

Answer 2

What you want is possible. You need to copy the value of MasterTypeID to each row of the Detail table so that the partition function can be applied to Detail.

Create a new column Detail.MasterTypeID and fill that column appropriately. Either in your application code or using a trigger in the database.

After the column is correctly filled you can apply the partition function.