I have a huge partition table with almost 1 billion records which is partitioned by date range i.e. from 2015-01-01 to 2017-12-01 with 1-36 partitions. There is a requirement of adding data to this table prior 2015 i.e. adding data of the year 2014. So i tried doing the below

ALTER PARTITION FUNCTION partition_function_name()
    SPLIT RANGE ( boundary_value ) };

But this can be done for adding the partitions after 2017 and I want to add it before 2015. Is there a way I can add the partitions prior 2015 efficiently without moving the existing data as the data is very huge.

1 Answer 1


Yes, but I wouldn't recommend it. Splitting partitions with lots of data takes an unpredictable amount of time and typically eats a ton of transaction log. I'd insert the data into a new table with the correct partitioning. It's more work, but you are in control of the move. Best practice (hindsight's 20/20) is to always have an empty left and right partition for these types of scenarios.

Best Practices

Always keep empty partitions at both ends of the partition range to guarantee that the partition split (before loading new data) and partition merge (after unloading old data) do not incur any data movement. Avoid splitting or merging populated partitions. This can be extremely inefficient, as this may cause as much as four times more log generation, and may also cause severe locking.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.