Splitting the Primary partition in

Question

On a table I have created a partition based on Year I have data from 2008 onwards I have created following partitions 2008, 2009, 2010 and Primary. Since primary has data for 2011 and 2012, I want to split the primary partition

I created a new file group for 2011. I ran the script for splitting the primary partition

ALTER PARTITION SCHEME [PartScheme] NEXT USED [FG2011]
GO
ALTER PARTITION FUNCTION PartFunction() split Range (20111231)
GO

after this the primary partition is getting split, but the data for the year 2011 is in primary partition and the data for the year is in 2011 partition.

partition_number    FileGroupName    value
1                    FG2008        20081231
2                    FG2009        20091231
3                    FG2010        20101231
4                    PRIMARY       20111231
5                    FG2011          NULL

I want the Partition for the current year that is 2012 to be in Primary. How can this be done?

was your partition function created using "RANGE LEFT" or "RANGE RIGHT"? Also, what's the query you used to generate the result set... — Bill Anton
It is using "Range Right" and regarding the Query to get Resultset is Good thats why i was able to display Result, but here i think the Result comes different regarding the Filegroup when we use Range " LEft or RIGHT" — user1407297
can you update your post with the query you ran to get the result set you posted? — Bill Anton

Stoleg Stoleg · Accepted Answer · 2013-06-04T12:40:52

Here is the script for the job:

alter partition function PF_MyFunction()  MERGE RANGE (20111231)
alter partition scheme PS_Myfunction next used [FG2011]
alter partition function PF_MyFunction()  SPLIT RANGE (20111231)

Here a script to check that your partition is actually on a different FG. Run it before and after.

SELECT 
     schema_name(c.schema_id)   [Schema_Name]
    ,object_name(a.object_id)   [Table_Name]
    ,a.name                     [Index_Name]
    ,a.type_desc                [Index_Type]
    ,d.Partition_Number         [Partition_Number]
    ,b.name                     [Logical_DataSpace Name or PS name]
    ,b.data_space_id            [Logical_DataSpaceID_PS_ID]
    ,f.name                     [Partition Function]
    ,f.function_id              [Partition Function ID]
    ,g.value                    [Partition Boundary Value]
    ,ISNULL (h.data_space_id, b.data_space_id) 
                                [Physiscal DataSpace ID]
    ,i.name                     [FileGroup Name]
    ,i.type_desc                [Physical Dataspace desc]

FROM (sys.indexes a inner join sys.tables c 
    on a.object_id = c.object_id) 
INNER JOIN sys.data_spaces b on a.data_space_id = b.data_space_id
INNER JOIN sys.partitions d on d.object_id = a.object_id
LEFT JOIN sys.partition_schemes e on e.data_space_id = b.data_space_id
LEFT JOIN sys.partition_functions f on f.function_id = e.function_id
LEFT JOIN sys.partition_range_values g on g.function_id = f.function_id and g.boundary_id = d.partition_number

LEFT JOIN sys.destination_data_spaces h 
            on h.partition_scheme_id  = e.data_space_id AND h.destination_id = d.partition_number

INNER JOIN sys.data_spaces i on i.data_space_id = isnull (h.data_space_id, b.data_space_id)

WHERE c.name = 'MyTable'

Splitting the Primary partition in

1 Answers