Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
SQL database in Microsoft Fabric Preview
Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.
Learn more about filegroups and partitioning strategies in Filegroups.
Note
In Azure SQL Database and SQL database in Microsoft Fabric Preview, only primary filegroups are supported.
Transact-SQL syntax conventions
Syntax
ALTER PARTITION SCHEME partition_scheme_name
NEXT USED [ filegroup_name ] [ ; ]
Arguments
partition_scheme_name
The name of the partition scheme to be altered.
filegroup_name
Specifies the filegroup to be marked by the partition scheme as NEXT USED. This means the filegroup accepts a new partition that is created by using an ALTER PARTITION FUNCTION statement.
In a partition scheme, only one filegroup can be designated NEXT USED. A filegroup that is not empty can be specified. If filegroup_name is specified and there currently is no filegroup marked NEXT USED, filegroup_name is marked NEXT USED. If filegroup_name is specified, and a filegroup with the NEXT USED property already exists, the NEXT USED property transfers from the existing filegroup to filegroup_name.
If filegroup_name is not specified and a filegroup with the NEXT USED property already exists, that filegroup loses its NEXT USED state so that there are no NEXT USED filegroups in partition_scheme_name.
If filegroup_name is not specified, and there are no filegroups marked NEXT USED, ALTER PARTITION SCHEME returns a warning.
Remarks
Any filegroup affected by ALTER PARTITION SCHEME must be online.
Permissions
The following permissions can be used to execute ALTER PARTITION SCHEME:
ALTER ANY DATASPACEpermission. This permission defaults to members of the sysadmin fixed server role and the db_owner and db_ddladmin fixed database roles.CONTROLorALTERpermission on the database in which the partition scheme was created.CONTROL SERVERorALTER ANY DATABASEpermission on the server of the database in which the partition scheme was created.
Examples
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 receives any additional partition of a partitioned table or index as a result of an ALTER PARTITION FUNCTION statement.