Partitioning Master Detail Table
database-partitioning, sql-server, sql-server-2008, sql-server-2008-r2
Solution
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.
Problem
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.