Sql-server – How to add values to partition function and scheme

partitioningsql server

We have a partition function defined as

CREATE PARTITION FUNCTION [PartitionByPeriodFunction](int) 
AS RANGE LEFT 
FOR VALUES (20101228, ... , 20161227)

where each date is the first date of our fiscal period.

We have a partition scheme defined as

CREATE PARTITION SCHEME [PartitionByPeriodScheme] 
AS PARTITION [PartitionByPeriodFunction] 
TO ([FactsBefore2011], ..., [FactsP201612], [FactsAfter2016])

I want to add more periods to the partition function and more filegroups to the partition scheme – what is the best way to do this?

I have read to drop and recreate the function and scheme – will the dependent objects allow that? I am unsure how to use split since I am not wanting to split 20161227 but rather continue the pattern.

Best Answer

You can modify the partition scheme and function to effectively add a new partition.

ALTER PARTITION SCHEME [PartitionByPeriodScheme] NEXT USED [FactsAfter2016_2];

Follow this up with:

ALTER PARTITION FUNCTION [PartitionByPeriodFunction]() SPLIT RANGE 20171231;

This will add a range between 20161227 and 20171231, which will be placed on [FactsAfter2016_2].

As always, try this on a non-production system first.

Related Solutions

Sql-server – Table Partitioning. What is the correct process for deleting .ndf and .ldf files

You can't delete the LDF file: it's essential.

For the NDF, you need to show that it's not used to SQL Server. You'd use DBCC SHRINKFILE with EMPTYFILE. Substitute xxx based on sys.database_files

DBCC SHRINKFILE (xx, EMPTYFILE)

How to add multiple partitions to a Greenplum table using dynamic SQL

This is happening because you are trying to alter the table to add partitions when there is a cursor open (active select) on the table. The required locks conflict. You can fix that by first fetching the partition dates into an array, close the cursor and then create partitions from that array. Code sample is below.

CREATE OR REPLACE FUNCTION dv_util.add_partitions(p_schema character varying, p_table character varying) RETURNS boolean AS
 $BODY$
 DECLARE
   adder varchar(10000); -- Query to generate all the partition parameters
   c_adding refcursor;   -- Cursor to iterate over the partiton parameters
   q_add RECORD;
   result boolean;
   counter integer;
   p_part_dates date[];
   part_cntr int;
 BEGIN

   adder :=          'with source as (SELECT * FROM '||p_schema||'.'||p_table||')';
   adder := adder || ', over as (SELECT * FROM '||p_schema||'.'||p_table||'_1_prt_overflow)';
   adder := adder || ', series as ';
   adder := adder || '(SELECT generate_series( greatest(';
   adder := adder || '        (   least( coalesce((select min(std_date_utc) from over),current_date)';
   adder := adder || '                 , coalesce((select (max(std_date_utc) + interval ''1 day'')::date from source)),current_date) - ''2001-01-01'')::integer';
   adder := adder || '                 , 0 )::integer';
   adder := adder || '      , (greatest( (select max(std_date_utc) from over)';
   adder := adder || '                 , current_date + interval ''30 day'')::date - ''2001-01-01'')::integer ))';
   adder := adder || 'select ((''2001-01-01''::date) + (generate_series * (interval ''1 day'')))::date dd from series';

   SELECT count(*) INTO counter FROM information_schema.tables WHERE table_schema = p_schema AND table_name = p_table||'_1_prt_overflow';
   IF counter > 0 THEN
     part_cntr := 0;
     FOR q_add IN EXECUTE adder LOOP
       part_cntr := part_cntr + 1;
       p_part_dates[part_cntr] = q_add.dd;
       -- SELECT dv_util.create_partition(p_schema,p_table,q_add.dd) INTO result;
     END LOOP;
     for i in 1..part_cntr loop
       select dv_util.create_partition(p_schema,p_table,p_part_dates[i]) INTO result;
     end loop;
     RETURN true;
   ELSE
     RETURN false;
   END IF;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE;

Best Answer

Related Solutions

Sql-server – Table Partitioning. What is the correct process for deleting .ndf and .ldf files

How to add multiple partitions to a Greenplum table using dynamic SQL

Related Question