Modifying SQL 2008/2012 PARTITIONS

Dear Experts;

My client has asked me to extend a project past it's expected end date of 2016.  The database has PARTITIONS through 2017 and I need to extend or create new PARTITIONS through 2020.  The problem I'm having is with the ALTER SCHEMA and FUNCTION - I just don't know what to do.  I have the ALTER DATABASE to create the FILEGROUPs example below, but cannot figure out the syntax for the SCHEMA and FUNCTION.

Experts, I need your help.  The original script that created the PARTITIONS AND SCHEMA is attached.

Thank you!

IF NOT EXISTS(SELECT * FROM HARBOR.sys.filegroups WHERE name = 'HARBOR20181001')
BEGIN
  ALTER DATABASE HARBOR
    ADD FILEGROUP HARBOR20181001

  ALTER DATABASE HARBOR
    ADD FILE ( NAME       = N'HARBOR20181001',
               FILENAME   = N'C:\Data\HARBOR20181001.ndf',
               SIZE       = 2048KB,
               MAXSIZE    = UNLIMITED,
               FILEGROWTH = 1024KB )
     TO FILEGROUP HARBOR20181001
END
Harbor.sql
wdbatesAsked:
Who is Participating?
 
dbaSQLConnect With a Mentor Commented:
You need to alter the partition function.  MSFT gives a decent example here.  Example A splits one of the existing partitions into two, thus creating a new partition:   https://technet.microsoft.com/en-us/library/ms186307.aspx

Or here's a quick example from Kalen Delaney.  The first statements The following statements will create 5 partitions with five different price boundary points:

CREATE PARTITION FUNCTION price_parts (money)
  AS RANGE LEFT FOR VALUES ( 1.00,10.00,100.00,1000.00) ;
GO

CREATE PARTITION SCHEME price_scheme
AS PARTITION price_parts
   ALL to ([PRIMARY]);
GO
SELECT * INTO NewProduct
FROM Production.Product;
GO  

CREATE CLUSTERED INDEX newproduct_pk
  ON NewProduct(ProductID)
  ON price_scheme (ListPrice);


Here we create a table for the partition:

SELECT * INTO NewProduct
FROM Production.Product;
GO  

CREATE CLUSTERED INDEX newproduct_pk
  ON NewProduct(ProductID)
  ON price_scheme (ListPrice);

Run this, you will see 5 partitions:
SELECT * FROM sys.partitions
WHERE object_id = object_id('dbo.NewProduct');

And here you split partition number 3, containing rows between 10 and 100:

ALTER PARTITION FUNCTION price_parts()
  SPLIT RANGE (50);
GO

Look again, you'll see six partitions:

SELECT * FROM sys.partitions
WHERE object_id = object_id('dbo.NewProduct');
0
 
wdbatesAuthor Commented:
Sorry for the delay in responding, but been on the road.  Life of the modern software developer.  Thank you for your help.
0
 
dbaSQLCommented:
My pleasure.  Glad things worked out for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.