Partition Table

Question

I am using SQL Server 2008 R2 and want to split the table of main tables and details. How can I split the Detail table into the MasterTypeID field in the main table.

My split function:

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

My separation scheme:

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

My main table structure:

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

The structure of my detailed table:

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

I want to split the partition table relative to the main partition. In other words, I want to save the record in the main table and the details associated with it in one file group.

Answer 1

You must define the MasterTypeID column in the Detail table and determine its permission to disable updating this column. and create a trigger in the Master table to synchronize the MasterTypeID column in the Master table with the MasterTypeID column in the Detail table.

Answer 2

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

Create a new Detail.MasterTypeID column and populate this column accordingly. Either in the application code, or using a trigger in the database.

After filling in the column correctly, you can apply the split function.