Can you add a "SplitPartition" function?

Mar 26, 2013 at 11:45 PM
Edited Mar 27, 2013 at 11:27 PM
In my current SSIS package l have an Execute SQL Task preceeding a call to ManagePartition.exe with the ClearPartition command. I've shown the code below.

There's some complexity there and it strikes me the ManagePartition.exe already has all the parameters in place to assist with this.

Perhaps it would be a new "SplitPartition" function?
-- SQL_Split_Partition
-- Parameter mapping to variable - same as /PartitionRangeValue
IF NOT EXISTS(
            SELECT prv.value, pf.name
            FROM sys.partition_functions AS pf
            JOIN sys.partition_range_values AS prv ON
                  prv.function_id = pf.function_id
            WHERE
                  pf.name = 'MyPartitionFunction'
                  AND CAST(prv.value AS datetime2) = ?
            )
BEGIN

    -- Prepare to Split a partition
    ALTER  PARTITION SCHEME  [MyPartitionScheme]
    NEXT USED [PRIMARY]

    -- Split a partition 
    ALTER PARTITION FUNCTION [MyPartitionFunction] ()
    SPLIT RANGE ( ? )

END