ALTER PARTITION SCHEME (Transact-SQL)

**APPLIES TO:** ![yes](media/yes.png)SQL Server (starting with 2008) ![yes](media/yes.png)Azure SQL Database ![no](media/no.png)Azure SQL Data Warehouse ![no](media/no.png)Parallel Data Warehouse

Adds a filegroup to a partition scheme or alters the designation of the NEXT USED filegroup for the partition scheme.

[!NOTE] In Azure SQL Database only primary filegroups are supported.

Article link icon Transact-SQL Syntax Conventions

Syntax

  
ALTER PARTITION SCHEME partition_scheme_name   
NEXT USED [ filegroup_name ] [ ; ]  

Arguments

partition_scheme_name
Is 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 will accept 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:

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 will receive any additional partition of a partitioned table or index as a result of an ALTER PARTITION FUNCTION statement.

See Also

CREATE PARTITION SCHEME (Transact-SQL)
DROP PARTITION SCHEME (Transact-SQL)
CREATE PARTITION FUNCTION (Transact-SQL)
ALTER PARTITION FUNCTION (Transact-SQL)
DROP PARTITION FUNCTION (Transact-SQL)
CREATE TABLE (Transact-SQL)
CREATE INDEX (Transact-SQL)
EVENTDATA (Transact-SQL)
sys.partition_schemes (Transact-SQL)
sys.data_spaces (Transact-SQL)
sys.destination_data_spaces (Transact-SQL)
sys.partitions (Transact-SQL)
sys.tables (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)