Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Partition Wizard


By:   |   Last Updated: 2013-01-09   |   Comments (9)   |   Related Tips: 1 | 2 | 3 | 4 | More > Partitioning

Problem

SQL Server Partitioning can be a bit confusing and can lead some companies in a different direction as far as archiving data. Writing the T-SQL code to create partition functions, partition schemes, and then altering the table to use the partition can be overwhelming for some DBA's. SQL Server 2008 introduced a table partitioning wizard in SQL Server Management Studio that helps make this task easier. In this tip, I'll show you how to use this wizard.  Let's jump in.

Solution

Based on my experience, it seems as if SQL Server table partitioning is not commonly used and I'm not sure if it's because of the complexity and management of partitions or if companies simply don't have the knowledge of partitioning data. There are a few tips that introduce you to partitioning that you can find here. This tip will focus on the SQL Server Partitioning wizard as opposed to the ins and outs of partitioning.

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, I'm using AdventureWorks2012.Production.TransactionHistory.

Start the SQL Server Partitioning Wizard in SQL Server Management Studio

On the Select a Partitioning Column screen as shown below you will need to 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, I'll choose TransactionDate so I'll partition my 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.

SQL Server Partition Wizard - Select a Partitioning Column

The next screen, Select a Partition Function, is where you will 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, I'll name the function TransactionHistoryFunction.

SQL Server Partition Wizard - Select a Partition Function

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

SQL Server Partition Wizard - Select a Partition Scheme

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

SQL Server Partition Wizard - Map 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.

SQL Server Partition Wizard - Set Boundary Values

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

SQL Server Partition Wizard - Map Partitions

Another cool feature of this wizard is the Estimate Storage button. If you click this button you can see the rowcount, required space, and available space as shown below.

SQL Server Partition Wizard - Map Partitions

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, I'll run immediately because we are working on a sample database rather than in production.

SQL Server Partition Wizard - Select an Output Option

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

SQL Server Partition Wizard - Review Summary

SQL Server Partition Wizard - Create Partition Wizard Progress

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
        
Query results for Partition Filegroup name, partition scheme, partition function and number of rows
Next Steps
  • To see more tips regarding partitioning click here
  • Once the partition is created, you can go back to the table and select Storage, Manage Partition and this will bring up a new wizard that will allow you to create staging tables, switch out partitions, switch in partitions, and manage partitioned data in a sliding window scenario.


Last Updated: 2013-01-09


next webcast button


next tip button



About the author





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 31, 2017 - 12:12:40 PM - Chris Back To Top

Thanks for the article!

If I run the following code to retrieve a rowcount of records that have a transaction date year of 2007 I get a value of 42844 which is different than the value of 1 that is displayed above in the Rowcount field for 2007 Filegroup:

select count(*) as '2007'

from [Production].[TransactionHistory]

where year(transactiondate) = 2007

 Please explain why based on the logic of this article the values do not match up.

 

Thanks!


Thursday, February 18, 2016 - 11:36:02 AM - Bob Back To Top

In the wizard's Map Partitions window, why does the "PRIMARY" file group need to be selected as well, when the partition function seems to only be creating three partitions for three years?


Tuesday, January 28, 2014 - 11:47:14 PM - Varinder Sandhu Back To Top

Hi,

Thanks for sharing this article. it is really helpful. but have one question you have used the index_id = 0 in where clause in the belwo script. what is use of this couloum because if we removed it then result will be different as shown in given image. can you given me some more detail about this.

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



Regards,
Varinder Sandhu

Monday, February 25, 2013 - 9:20:05 AM - sonu Back To Top

Hi

 

I didn't get the steps for once the partition is created  like select and join. I have managed to create partion as per your instructions and now am stuck.

regards

sonu


Friday, February 22, 2013 - 1:26:33 PM - Brady Back To Top

JRubin,

When you create your filegroups, put them on seperate drives and then use the partition wizard to select multiple filegroups.


Friday, February 22, 2013 - 11:11:24 AM - JRubin Back To Top

Thank you for the article. Does the wizard also allow you put specific partitions on a separate drive?


Thursday, January 10, 2013 - 4:08:52 AM - Ian Stirk Back To Top

Very clear and concise example, thank you! Ian


Wednesday, January 09, 2013 - 12:07:41 PM - Observa Back To Top

For anyone new who doesn't know this, Partitioning is only availabe in Enterprise Edition.

It is possible to manually parition data and create the underlying triggers, etc. in Standard Edition. This is how the concepts came into being 'back in the day'.

Enterprise Edition (at a high cost) helps simplify this activity and is tuned to better deal with this than what most beginners can implement in Standard Edition by hand.


Wednesday, January 09, 2013 - 10:32:18 AM - Anbu Back To Top

Thanks a lot for nice article


Learn more about SQL Server tools