Skip to content

alter-partition-scheme-transact-sql.md

Latest commit

 

History

History
96 lines (73 loc) · 5.14 KB

alter-partition-scheme-transact-sql.md

File metadata and controls

96 lines (73 loc) · 5.14 KB
title ALTER PARTITION SCHEME (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/06/2017
ms.prod sql
ms.prod_service sql-database
ms.reviewer
ms.technology t-sql
ms.topic language-reference
f1_keywords
ALTER PARTITION SCHEME
ALTER_PARTITION_SCHEME_TSQL
dev_langs
TSQL
helpviewer_keywords
ALTER PARTITION SCHEME statement
partition schemes [SQL Server], modifying
modifying partition schemes
adding filegroups
NEXT USED filegroups
ms.assetid f01d6880-9800-4cfb-8d11-d4be21efc8ca
author CarlRabeler
ms.author carlrab

ALTER PARTITION SCHEME (Transact-SQL)

[!INCLUDE SQL Server SQL Database]

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:

  • 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.

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)