CREATE PARTITION SCHEME [FY] AS PARTITION [FY] TO ([PRIMARY], [PRIMARY], [PRIMARY], [PRIMARY]);
GO
CREATE PARTITION FUNCTION [FY](INT) AS RANGE LEFT FOR VALUES (20110700, 20120700, 20130700);

ALTER PARTITION SCHEME [FY] NEXT USED [PRIMARY];
GO
ALTER PARTITION FUNCTION [FY] () SPLIT RANGE (20140700);

USE AdventureWorks2012;
GO
-- add five new filegroups to the AdventureWorks2012 database
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test1fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test2fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test3fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test4fg;
GO
ALTER DATABASE AdventureWorks2012
ADD FILEGROUP test5fg;
GO
-- if the "myRangePF1" partition function and the "myRangePS1" partition scheme exist,
-- drop them from the AdventureWorks2012 database
IF EXISTS (SELECT * FROM sys.partition_functions
    WHERE name = 'myRangePF1')
DROP PARTITION FUNCTION myRangePF1;
GO
IF EXISTS (SELECT * FROM sys.partition_schemes
    WHERE name = 'myRangePS1')
DROP PARTITION SCHEME myRangePS1;
GO
-- create the new partition function "myRangePF1" with four partition groups
CREATE PARTITION FUNCTION myRangePF1 (int)
AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
GO
-- create the new partition scheme "myRangePS1"that will use 
-- the "myRangePF1" partition function with five file groups.
-- The last filegroup, "test5fg," will be kept empty but marked
-- as the next used filegroup in the partition scheme.
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg, test5fg);
GO
--Split "myRangePS1" between boundary_values 100 and 1000
--to create two partitions between boundary_values 100 and 500
--and between boundary_values 500 and 1000.
ALTER PARTITION FUNCTION myRangePF1 ()
SPLIT RANGE (500);
GO
-- Allow the "myRangePS1" partition scheme to use the filegroup "test5fg"
-- for the partition with boundary_values of 100 and 500
ALTER PARTITION SCHEME myRangePS1
NEXT USED test5fg;
GO