ALTER PARTITION SCHEME (Transact-SQL)
Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.
Applies to: SQL Server (SQL Server 2008 through current version).
Tthe following permissions can be used to execute ALTER PARTITION SCHEME:
ALTER ANY DATASPACE permission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.
CONTROL or ALTER permission on the database in which the partition scheme was created.
CONTROL SERVER or ALTER ANY DATABASE permission on the server of the database in which the partition scheme was created.
The following example assumes the partition scheme MyRangePS1 and the filegroup test5fg exist in the current database.
ALTER PARTITION SCHEME MyRangePS1 NEXT USED test5fg;
Filegroup test5fg will receive any additional partition of a partitioned table or index as a result of an ALTER PARTITION FUNCTION statement.