Export (0) Print
Expand All

Modify a Partition Scheme

You can modify a partition scheme in SQL Server 2014 by designating a filegroup to hold the next partition that is added to a partitioned table using SQL Server Management Studio or Transact-SQL. You do this by assigning the NEXT USED property to a filegroup. You can assign the NEXT USED property to an empty filegroup or to one that already holds a partition. In other words, a filegroup can hold more than one partition.

In This Topic

Limitations and Restrictions

Any filegroup affected by ALTER PARTITION SCHEME must be online.

Security

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.

Arrow icon used with Back to Top link [Top]

To modify a partition scheme:

This specific action cannot be performed using SQL Server Management Studio. In order to modify a partition scheme, you must first delete the scheme and then create a new one with the desired properties using the Create Partition Wizard. For more information, see Create Partitioned Tables and IndexesUsing SQL Server Management Studio under Create Partitioned Tables and Indexes.

To delete a partition scheme

  1. Click the plus sign to expand the database where you want to delete the partition scheme.

  2. Click the plus sign to expand the Storage folder.

  3. Click the plus sign to expand the Partition Schemes folder.

  4. Right-click the partition scheme you want to delete and select Delete.

  5. In the Delete Object dialog box, ensure that the correct partition scheme is selected, and then click OK.

Arrow icon used with Back to Top link [Top]

To modify a partition scheme

  1. In Object Explorer, connect to an instance of Database Engine.

  2. On the Standard bar, click New Query.

  3. Copy and paste the following example into the query window and click Execute.

    USE AdventureWorks2012;
    GO
    -- add five new filegroups to the AdventureWorks2012 database
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test1fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test2fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test3fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test4fg;
    GO
    ALTER DATABASE AdventureWorks2012
    ADD FILEGROUP test5fg;
    GO
    -- if the "myRangePF1" partition function and the "myRangePS1" partition scheme exist,
    -- drop them from the AdventureWorks2012 database
    IF EXISTS (SELECT * FROM sys.partition_functions
        WHERE name = 'myRangePF1')
    DROP PARTITION FUNCTION myRangePF1;
    GO
    IF EXISTS (SELECT * FROM sys.partition_schemes
        WHERE name = 'myRangePS1')
    DROP PARTITION SCHEME myRangePS1;
    GO
    -- create the new partition function "myRangePF1" with four partition groups
    CREATE PARTITION FUNCTION myRangePF1 (int)
    AS RANGE LEFT FOR VALUES ( 1, 100, 1000 );
    GO
    -- create the new partition scheme "myRangePS1"that will use 
    -- the "myRangePF1" partition function with five file groups.
    -- The last filegroup, "test5fg," will be kept empty but marked
    -- as the next used filegroup in the partition scheme.
    CREATE PARTITION SCHEME myRangePS1
    AS PARTITION myRangePF1
    TO (test1fg, test2fg, test3fg, test4fg, test5fg);
    GO
    --Split "myRangePS1" between boundary_values 100 and 1000
    --to create two partitions between boundary_values 100 and 500
    --and between boundary_values 500 and 1000.
    ALTER PARTITION FUNCTION myRangePF1 ()
    SPLIT RANGE (500);
    GO
    -- Allow the "myRangePS1" partition scheme to use the filegroup "test5fg"
    -- for the partition with boundary_values of 100 and 500
    ALTER PARTITION SCHEME myRangePS1
    NEXT USED test5fg;
    GO
    

For more information, see ALTER PARTITION SCHEME (Transact-SQL).

Arrow icon used with Back to Top link [Top]

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft