Partition functions

A partition function is a database object that defines how to map rows of a table or index into partitions based on the values of a specified column.

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

Create a partition function

1. On the SQL toolbar, click New SQL.

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

CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )  
AS RANGE [ LEFT | RIGHT ]   
FOR VALUES ( [ boundary_value [ ,...n ] ] )   
[ ; ]

where:

• partition_function_name is the name of the partition function you want to create.
• input_parameter_type is the data type of the column used for partitioning.
• boundary_value specifies boundary values for each partition of a table or index that uses partition_function_name. If no boundary values are provided, the entire table or index is placed into a single partition. Only one partitioning column, specified in the CREATE TABLE or CREATE INDEX statement, can be used.

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

Delete a partition function

1. In Database Explorer, right-click the partition function 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 all its data and structure. It is recommended that you back up the database before deleting it.

Duplicate a partition function

1. In Database Explorer, right-click a required partition function 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 function

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

2. Select the output option:

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

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

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

Refresh partition functions

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

Open the Properties window

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

Filter partition functions

1. In Database Explorer, right-click the Partition Functions 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 Functions 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 function

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

Shortcut menu options for the Partition Functions node

The following table describes the shortcut menu options, which appear when you right-click the Partition Functions 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 functions. Then right-click the selection and select the required option.

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