Manage Partition Wizard
Applies to: 
SQL Server
Use the Manage Partition Wizard to manage and modify existing partitioned tables through partition switching or the implementation of a sliding window scenario. This wizard can ease the management of your partitions and simplify the regular migration of data in and out of your tables.
To start the Manage Partition Wizard
In SQL Server Management Studio, select the database, right-click the table on which you want to create partitions, point to Storage, and then click Manage Partition.
Note If Manage Partition is unavailable, you may have selected a table that does not contain partitions. Click Create Partition on the Storage submenu and use the Create Partition Wizard to create partitions in your table.
For general information about partitions and indexes, see Partitioned Tables and Indexes.
This section provides the information that is required to manage, modify, and implement partitions using the Manage Partition Wizard.
In This Section
The following sections provide help on the pages of the Manage Partition Wizard.
Manage Partition Wizard (Select Partition Action Page)
Manage Partition Wizard (Switch In Page)
Manage Partition Wizard (Switch Out Page)
Manage Partition Wizard (Select Staging Table Options Page)
Manage Partition Wizard (Select Output Option Page)
Manage Partition Wizard (New Job Schedule Page)
Manage Partition Wizard (Summary Page)
Manage Partition Wizard (Progress Page)
Select Partition Action Page
Use the Select Partition Action page to choose the action you want to perform on your partition.
Create a Staging Table
Partition switching is a common partitioning task if you have a partitioned table that you migrate data into and out of on a regular basis; for example, you have a partitioned table that stores current quarterly data, and you must move in new data and archive older data at the end of each quarter.
The wizard designs the staging table with the same partitioning column, table and column structure, and indexes, and stores the new table in the filegroup where your source partition is located.
To create a staging table to switch in or switch out partition data, select Create a staging table for partition switching.
Sliding Window Scenario
To manage your partitions in a sliding-window scenario, select Manage partitioned data in a sliding window scenario.
UI element list
Create a staging table for partition switching
Creates a staging table for the data you are switching in or switching out of the existing partitioned table.
Switch out partition
Provides options when removing a partition from the table.
Switch in partition
Provides options when adding a partition to the table.
Manage partitioned data in a sliding window scenario
Appends an empty partition to the existing table that can be used for switching in data. The wizard currently supports switching into the last partition and switching out the first partition.
Select Partition Switching-In Options Page
Use the Select Partition Switching-In options page to select the staging table you are switching into the partitioned table.
UI element list
Show All Partitions
Select to show all partitions, including the partitions currently in the partitioned table.
Partition grid
Displays the partition name, Left boundary, Right boundary, Filegroup, and Row count of the partitions you selected.
Switch in table
Select the staging table that contains the partition that you want to add to your partitioned table. You must create this staging table before you switch-in partitions with the Manage PartitionsWizard.
Select Partition Switching-Out Options Page
Use the Select Partition Switching-Out options page to select the partition and the staging table to hold the partitioned data that you are switching out of the partitioned table.
UI element list
Partition grid
Displays the partition name, Left boundary, Right boundary, Filegroup, and Row count of the partitions you selected.
Switch out table
Choose a new table or an existing table to switch-out your data to.
New
Enter a new name for the staging table you want to use for the partition to switch out of the current source table.
Existing
Select an existing staging table you want to use for the partition you want to switch out of the current source table. If the existing table contains data, this data will be overwritten with the data you are switching out.
Select the Staging Table Options Page
Use the Select the Staging Table Options page to create the staging table you want to use for switching your partitioned data.
Staging tables must reside in the same filegroup of the selected partition where the source table is located. The staging table must mirror the design of both the source table and the destination table.
You can also create the same indexes in the staging table that exist in the source partition. The staging table automatically contains a constraint based on the elements of the source partition. This constraint is typically generated from the boundary value of the source partition.
UI element list
Staging table name
Create a name for the staging table or accept the default name displayed in the edit box.
Switch partition
Select the source partition that you want to switch out of the current table.
New boundary value
Select or enter the boundary value you want for the partition in the staging table.
Filegroup
Select a filegroup for the new table.
Select Output Option Page
Use the Select Output Option page to specify how you want to complete the modifications to your partitions.
Create Script
When the wizard finishes, it creates a script in Query Editor to modify partitions in the table. Select Create Script if you want to review the script, and then execute the script manually.
Script to file
Generate the script to a .sql file. Specify either Unicode or ANSI text. To specify a name and location for the file, click Browse.
Script to Clipboard
Save the script to the Clipboard.
Script to New Query Window
Generate the script to a Query Editor window. If no editor window is open, a new editor window opens as the target for the script.
Run Immediately
Run immediately
Have the wizard finish modifications to the partitions when you click Next or Finish.
Schedule
Select to modify the table partitions at a scheduled date and time.
Change schedule
Opens the New Job Schedule dialog box, where you can select, change, or view the properties of the scheduled job.
New Job Schedule Page
Use the New Job Schedule page to view and change the properties of the schedule.
Options
Select the type of schedule you want for the SQL Server Agent job.
Name
Type a new name for the schedule.
Jobs in schedule
View the existing jobs that use the schedule.
Schedule type
Select the type of schedule.
Enabled
Enable or disable the schedule.
Recurring Schedule Types Options
Select the frequency of the scheduled job.
Occurs
Select the interval at which the schedule recurs.
Recurs every
Select the number of days or weeks between recurrences of the schedule. This option is not available for monthly schedules.
Monday
Set the job to occur on a Monday. Only available for weekly schedules.
Tuesday
Set the job to occur on a Tuesday. Only available for weekly schedules.
Wednesday
Set the job to occur on a Wednesday. Only available for weekly schedules.
Thursday
Set the job to occur on a Thursday. Only available for weekly schedules.
Friday
Set the job to occur on a Friday. Only available for weekly schedules.
Saturday
Set the job to occur on a Saturday. Only available for weekly schedules.
Sunday
Set the job to occur on a Sunday. Only available for weekly schedules.
Day
Select the day of the month the schedule occurs. Only available for monthly schedules.
of every
Select the number of months between occurrences of the schedule. Only available for monthly schedules.
The
Specify a schedule for a specific day of the week on a specific week within the month. Only available for monthly schedules.
Occurs once at
Set the time for a job to occur daily.
Occurs every
Set the number of hours or minutes between occurrences.
Start date
Set the date when this schedule will become effective.
End date
Set the date when the schedule will no longer be effective.
No end date
Specify that the schedule will remain effective indefinitely.
One Time Schedule Types Options
If you schedule a job to run once, you must select a date and time in the future.
Date
Select the date for the job to run.
Time
Select the time for the job to run.
Summary Page
Use the Summary page to review the options that you have selected on the previous pages.
UI element list
Review your selections
Displays the selections you have made for each page of the wizard. Click a node to expand and view your previously selected options.
Progress Page
Use the Progress page to monitor status information about the actions of the Manage Partition Wizard. Depending on the options that you selected in the wizard, the Progress page might contain one or more actions. The top box displays the overall status of the wizard and the number of status, error, and warning messages that the wizard has received.
Options
Details
Provides the action, status, and any messages that are returned from action taken by the wizard.
Action
Specifies the type and name of each action.
Status
Indicates whether the wizard action as a whole returned the value of Success or Failure.
Message
Provides any error or warning messages that are returned from the process.
Stop
Stop the action of the wizard.
Report
Create a report that contains the results of the Manage Partition Wizard. The options are:
View Report
Save Report to File
Copy Report to Clipboard
Send Report as Email
View Report
Open the View Report dialog box. This dialog box contains a text report of the progress of the Manage Partition Wizard.
Close
Close the wizard.
See Also
Feedback
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback.
Submit and view feedback for