Partition schemes

A partition scheme is a database object that maps the partitions created by a partition function to a set of filegroups you define.

In Database Explorer, you can perform operations on partition schemes by using the shortcut menu. Right-click a partition scheme in Database Explorer to access commands for specific tasks, such as generating a CREATE script or duplicating an object.

Create a partition scheme

1. On the SQL toolbar, click New SQL.

2. In a SQL document, enter the CREATE PARTITION SCHEME statement.

CREATE PARTITION SCHEME partition_scheme_name
AS PARTITION partition_function_name
[ ALL ] TO ( { file_group_name | [ PRIMARY ] } [ , ...n ] )
[ ; ]

where:

• partition_scheme_name is the name of the partition scheme you want to create.
• partition_function_name is the name of the partition function that uses the partition scheme.

• file_group_name is the name of the filegroup that contains the partitions specified by partition_function_name. file_group_name must already exist in the database.

  If PRIMARY is specified, the partition is stored on the primary filegroup. If ALL is specified, only one file_group_name can be set.

3. On the SQL toolbar, click Execute, or press F5.

Delete a partition scheme

1. In Database Explorer, right-click the partition scheme you want to delete and select Delete, or press Delete.

2. Click Yes to confirm the deletion.

Note

You cannot undo the drop operation, as this action permanently deletes the partition scheme. To avoid data loss, back up the database before deleting the partition scheme.

Duplicate a partition scheme

1. In Database Explorer, right-click a required partition scheme and select Duplicate Object.

2. In the Duplicate Object dialog, configure the object settings:

• In Destination connection, select the target server.
• In Destination database, select the target database.
• In Destination schema, select the target schema.
• In New object name, specify the object name.
• Select Drop destination object to drop the destination object if it already exists.
• Optional: Select Script Changes, then select To New SQL Window to open the generated script in a new SQL document, or To Clipboard to copy it to the clipboard.

3. Click OK.

Generate a script for a partition scheme

1. In Database Explorer, right-click a required partition scheme and select Generate Script As, then select the statement type.

2. Select one of the following output options:

• To New SQL Window – Opens the script in a new SQL document.
• To File – Generates the script to a .sql file.
• To Clipboard – Copies the script to the clipboard.

For more information, see Generate the DDL and DML statements for database objects.

View properties of a partition scheme

To view properties of a partition scheme, in Database Explorer, right-click the partition scheme or Partition Schemes node and select Show Details.

Object Viewer opens, displaying detailed information about the partition scheme.

Refresh partition schemes

To update the partition scheme to reflect the latest changes, in Database Explorer, right-click the partition scheme or Partition Schemes node and select Refresh, or press F5.

Open the Properties window

To open the Properties window, in Database Explorer, right-click the partition scheme and select Properties, or press F4.

Filter partition schemes

1. In Database Explorer, right-click the Partition Schemes node and select Filter.

2. In the Filter Settings dialog, configure the filter parameters.

3. Click OK to apply the filter.

Note

When you open the Partition Schemes node, the number of objects it contains appears in parentheses next to the node name. This number changes based on your filter settings.

Shortcut menu options for a specific partition scheme

The following table describes the shortcut menu options, which appear when you right-click a specific partition scheme in Database Explorer.

Shortcut menu options for the Partition Schemes node

The following table describes the shortcut menu options, which appear when you right-click the Partition Schemes node in Database Explorer.

Note

The Source Control shortcut menu is available only after you link the corresponding database to a source-control system.

Bulk operations

In Database Explorer, hold down Ctrl and select multiple partition schemes. Then right-click the selection and select the required option.

The following table describes the shortcut menu options available for multiple partition schemes.