Listing 2: Code to Add a Partition for Data >= 2005
-- Add a filegroup with file(s) for >= 2005
-- Alter partition scheme to mark new fg as next used
-- Split empty >=2004 partition to =2004, >=2005 by 
-- altering the partition function adding new boundary '20050101'
ALTER DATABASE testdb ADD FILEGROUP FG_Orders2005;

ALTER DATABASE testdb ADD FILE
(
    NAME = 'testdb_Data_Orders2005_1',
    FILENAME = 'c:\temp\testdb_Data_Orders2005_1.ndf'
) 
TO FILEGROUP FG_Orders2005;

ALTER PARTITION SCHEME PS_Orders 
NEXT USED FG_Orders2005;

-- Actual split
-- To verify that no I/O is involved run with STATISTICS IO turned on
SET STATISTICS IO ON

ALTER PARTITION FUNCTION PF_RangeByYear()
SPLIT RANGE ('20050101');