SQL Server Partition Wizard

The T-SQL code to create partition functions, partition schemes, and then altering the table to use the partition can be overwhelming for some database administrators. Microsoft SQL Server 2008 introduced a table partitioning wizard in SQL Server Management Studio that helps make this task easier.

Solution

To start the wizard, right click on the table you want to partition in SQL Server Management Studio and select Storage, Create Partition. In this example, the AdventureWorks2012.Production.TransactionHistory is used.

On the Select a Partitioning Column screen as shown below select the column that you want to use to partition your table. The most common column used is a date column since most tables are being archived by date. In this example, choose “TransactionDate” partitioning the table based on the date when the transaction was entered into this table.

Other options include:

Collocate this table to the selected partition table: Displays related data to join with the column being partitioned.

Storage Align Non Unique Indexes and Unique Indexes with an Indexed Partition Column: Aligns all indexes of the table being partitioned with the same partition scheme. If you do not select this option, you may place indexes independently of the columns they point to.

The next screen, Select a Partition Function, create the partition function. This will create a function that maps the rows of the table or index into partitions based on the values of the TransactionDate column. In this example name the function TransactionHistoryFunction.

The next screen, Select a Partition Scheme, create the partition scheme. This will create a scheme that maps the partitions of the Production.TransactionHistory table to different filegroups. In this example name the scheme TransactionHistoryScheme.

The next screen, Map Partitions, is where you will map your partitions.

The range and values are as follows:

Filegroup: Filegroup used for the partition.

Boundary: Used for entering range values up to a specified value. Left boundary is based on Value <= Boundary and Right boundary is based on Value < Boundary.

RowCount: Read-only columns that display required space and are determined only when the Estimate Storage button is clicked.

Required Space: Read-only columns that display required space and are determined only when the Estimate Storage button is clicked.

Available Space: Read-only columns that display available space and are determined only when the Estimate Storage button is clicked.

Estimate Storage: When selected, this option determines the rowcount, required, and available space.

If you click the Set Boundaries button the wizard will generate boundaries for you. This is a nice feature because it’s easy to change and doesn’t involve any complexity.

Click OK on the Set Boundary Values screen and you will notice it separates the boundaries into 3 different rows based on years. Three new filegroups have been created that will change to match each boundary.

Another feature of this wizard is the Estimate Storage button. Click this button to see the rowcount, required space, and available space as shown below.

The next screen is the Output screen. This screen allows you to generate the T-SQL, run immediately, or create a schedule to execute. In this example, run immediately because it is a sample database rather than a production DB.

The final screen is the Summary screen that allows you to review your selections. Click the Finish button to partition your table.

Once the table is partitioned, you can run the following query to verify the filegroup name, partition scheme, partition function, and number of rows in each filegroup.

SELECT f.name AS FG, sps.name AS PartitionScheme, spf.name as PartitionFunction, p.rows

FROM sys.partitions p

JOIN sys.destination_data_spaces dds ON p.partition_number = dds.destination_id

JOIN sys.partition_schemes sps ON dds.partition_scheme_id = sps.data_space_id

JOIN sys.partition_functions spf ON sps.function_id = spf.function_id

JOIN sys.filegroups f ON dds.data_space_id = f.data_space_id

WHERE OBJECT_NAME(OBJECT_ID) = ‘TransactionHistory’ and index_id = 0

For more Microsoft Technical Training information visit www.directionstraining.com or call 1-855-575-8900.