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.n
SIZE = 2048KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
TO FILEGROUP HARBOR20181001
END
Harbor.sql
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.n
SIZE = 2048KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB )
TO FILEGROUP HARBOR20181001
END
Harbor.sql
Who is Participating?
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.
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'