SQL Server — add partitions to partitioned table

There are a lot of situations when you need to add new partitions to an existing, partitioned table. We can perform it in a pretty simple way.

I’ve created a partitioned table with a following partition function.

CREATE PARTITION FUNCTION udf_PartitionYear(date) 
 AS RANGE RIGHT 
 FOR VALUES
 (
   '2000-01-01'
  ,'2001-01-01'
  ,'2002-01-01'
  ,'2003-01-01'
  ,'2004-01-01'
  ,'2005-01-01'
  ,'2006-01-01'
  ,'2007-01-01'
  ,'2008-01-01'
  ,'2009-01-01'
  ,'2010-01-01'
  ,'2011-01-01'
  ,'2012-01-01'
  ,'2013-01-01'
  ,'2014-01-01'
  ,'2015-01-01'
  ,'2016-01-01'
  ,'2017-01-01'
  ,'2018-01-01'
  ,'2019-01-01'
  ,'2020-01-01'
  ,'2021-01-01'
 );

And a partition schema.

CREATE PARTITION SCHEME ups_PartionYear 
    AS PARTITION udf_PartitionYear
    ALL TO ([PRIMARY])

As you can see there are 23 partitions.

SELECT 
  partition_id, 
  partition_number, 
  used_page_count, 
  row_count 
FROM sys.dm_db_partition_stats 
WHERE OBJECT_ID = OBJECT_ID('dbo.GHCN_PARTITION')

Let’s assume that we want to create partitions for a range lower than 2000–01–01.

We need to modify the partition schema and the partition function with a following pattern.

ALTER PARTITION scheme ups_partionYear NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION udf_partitionYear() SPLIT RANGE ('1999–01–01')
ALTER PARTITION scheme ups_partionYear NEXT USED [PRIMARY]
ALTER PARTITION FUNCTION udf_partitionYear() SPLIT RANGE ('1998–01–01')

…and the same way for previous years.

As you can see, the partitions have been added.