Partitioning Using the Wizard in SQL Server

Introduction

In my earlier articles of the series, I discussed what partitioning in SQL Server is; the different kinds of partitioning options that we have, why and when we should go for partitioning and what benefits partition table/index provides. Then I talked about different partitioning concepts, such as partition function, partition scheme, guideline on choosing partitioning column and creating partition on a table or index. We also learned about partitioned index and how an index is aligned and storage aligned with the base table partitioning.

In my last article of this series, I provided a step-by-step guide on creating a partition table/index using T-SQL commands. In this article I am going to provide you a step-by-step guide on creating partition table/index using the Wizards in SQL Server Management Studio (SSMS) and talk about Partition Aligned Index Views.

Getting Started with Partitioning in SQL Server with Wizards in SQL Server Management Studio (SSMS)

We’ll do the same as we did in the last example, only this time we’ll use the wizards in SSMS instead of writing T-SQL commands. Below is what I want to achieve using the wizard:

  • Create a partition function, which will divide data on a yearly basis starting from 2005 until 2012.
  • Create a partition scheme, which will use the above created partition function and will map partitions of the table to the file groups.
  • Create a partitioned table from [dbo].[FactResellerSales] table of the [AdventureWorksDW2012] database, which will have only a few columns (I have omitted the other columns for the sake of simplicity).
  • Load some data and analyze data going to correct partitions.

First of all, let’s create an un-partitioned table using the script provided below. As you can see, the ON clause specifies the PRIMARY file group on which the table will be created:

USE [AdventureWorksDW2012]
GO
CREATE TABLE [dbo].[FactResellerSalesWithPartition](
       [ProductKey] [int] NOT NULL,
       [OrderDateKey] [int]   NOT NULL,
       [DueDateKey] [int] NOT NULL,
       [ResellerKey] [int]   NOT NULL,
       [SalesOrderNumber] [nvarchar](20) NOT NULL,
       [SalesOrderLineNumber] [tinyint]   NOT NULL,
       [OrderQuantity] [smallint]   NULL,
       [UnitPrice] [money]   NULL,
       [SalesAmount] [money]   NULL
) ON   [Primary]

I want to partition the above table to divide the data on a yearly basis starting from 2005 to 2012 using the LEFT range as shown in the image below:

The LEFT range
The LEFT range

Right click on the table in the Object Explorer of SSMS and click on Storage -> Create Partition menu item as shown below:

Create Partition
Create Partition

The first screen of the wizard is the Welcome screen and it might or might not appear based on settings; click on the Next button to move to the next screen of the wizard:

Welcome to the Create Partition Wizard
Welcome to the Create Partition Wizard

On the next screen of the wizard, you need to choose one of the columns from the table, which will be used as a partitioning column. You can also select the option to storage align all the unique and non-unique indexes with the indexed partitioning column. Click on the Next button to move on to the next screen of the wizard.

I have provided some guidelines on selecting partitioning column in my last article of the series.

Create Partition Wizard
Create Partition Wizard

On the next screen of the wizard, you need to choose partition function if one already exists or specify to create a new partition function. As we are doing it for first time and want to create a new partition function, I have chosen to create a new partition function as shown below.

Select a Partition Function
Select a Partition Function

On the next screen of the wizard, you need to choose a partition scheme, if one already exists, or specify to create a new partition scheme. As we are doing it for first time and want to create a new partition scheme, I have chosen to create a new partition scheme as shown below.

Select a Partition Scheme
Select a Partition Scheme

On the next screen of the wizard, you need to first specify the LEFT (<=, which means the boundary value will fall to the left side of the partition) or RIGHT (<, which means the boundary value will fall to the right side of the partition) range. Next you need to choose the file groups and specify boundary values for each file groups. As you might have noticed, I have specified one more file group than the number of boundary values specified; this is because the number of partitions created will be N+1 where N is the number of boundary values specified. You can click on the Estimate Storage button to get an estimate on required space vs. available space based on number of records in the table.

Map Partitions
Map Partitions

On the next screen of the wizard, you specify your choice of whether you want the script to be executed immediately by the wizard to create these objects and partition table, or you want to save script to run it later. You can also specify a schedule when the script will be executed to perform the operations automatically.

Select an Output Option
Select an Output Option

On the next or final screen of the wizard, you can verify your selection and click on the Finish button to create all the objects, if needed, and partition the table.

Review Your Selections
Review Your Selections

As we have made the above created table as a partitioned table, we can verify it using the query below, which will tell boundary values for data distribution.

SELECT r.value, f.* FROM sys.partition_functions   f
INNER JOIN sys.partition_range_values   r ON f.function_id = r.function_id 
WHERE f.name = 'FactPartitionFunction'
GO

Verify the Query
Verify the Query

So far so good, we have created a partition function, a partition scheme and a partitioned table using the wizard in SSMS and we also have verified that the table has partitions. Now let’s load some data into the table and see how data is distributed across different partitions (internally by SQL Server) based on the distribution rule specified with the partition function we created using the wizard.

USE [AdventureWorksDW2012]
INSERT INTO [dbo].[FactResellerSalesWithPartition] (ProductKey,OrderDateKey,DueDateKey,ResellerKey,SalesOrderNumber,SalesOrderLineNumber,OrderQuantity,UnitPrice,SalesAmount)
SELECT ProductKey,OrderDateKey,DueDateKey,ResellerKey,SalesOrderNumber,SalesOrderLineNumber,OrderQuantity,UnitPrice,SalesAmount FROM [dbo].[FactResellerSales]

Once you are done with loading data into the table, you can execute the query below to see how many records moved into or exist in each partition of the table. You can see there are 4138 records from year 2005 and hence residing in partition number 1 (which is for 2005 from the partition function definition), there are 16676 records from year 2006 and hence residing in partition number 2 (which is for 2006 from the partition function definition) and so on.

SELECT t.object_id, t.name, p.partition_id, p.partition_number, p.rows FROM sys.partitions AS p
INNER JOIN sys.tables AS t  ON  p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL AND t.name = 'FactResellerSalesWithPartition'   ORDER BY p.partition_number 
GO

How many records moved or exist in each partition 
How many records moved or exist in each partition

Now let’s insert some individual records and see how they are moved to appropriate partitions. As you can see below, I have 3 records, 2 of them are from year 2012 and 1 of them is from year 2013 as highlighted below.

--2012
INSERT [dbo].[FactResellerSalesWithPartition] ([ProductKey], [OrderDateKey], [DueDateKey], [ResellerKey], [SalesOrderNumber],   [SalesOrderLineNumber], [OrderQuantity], [UnitPrice], [SalesAmount]) 
VALUES (345, 20121201, 20051213, 581, N'SO44757', 5, 4, 2039.9940,   8159.9760)
INSERT [dbo].[FactResellerSalesWithPartition] ([ProductKey], [OrderDateKey], [DueDateKey], [ResellerKey], [SalesOrderNumber],   [SalesOrderLineNumber], [OrderQuantity], [UnitPrice], [SalesAmount]) 
VALUES (345, 20121231, 20051213, 581, N'SO44757', 5, 4, 2039.9940,   8159.9760)
--2013
INSERT [dbo].[FactResellerSalesWithPartition] ([ProductKey], [OrderDateKey], [DueDateKey], [ResellerKey], [SalesOrderNumber],   [SalesOrderLineNumber], [OrderQuantity], [UnitPrice], [SalesAmount]) 
VALUES (345, 20130101, 20051213, 581, N'SO44757', 5, 4, 2039.9940,   8159.9760)
 

Now let’s verify again the partition wise record distribution using the script provided below. If you notice, out of those 3 records we inserted above, there are 2 records from year 2012 residing in partition number 8 (which is for 2012 from the partition function definition) and there is 1 record from the year 2013 residing in partition number 9 (which is for 2013+ from the partition function definition).

SELECT t.object_id, t.name, p.partition_id, p.partition_number, p.rows FROM sys.partitions AS p
INNER JOIN sys.tables AS t  ON  p.object_id = t.object_id
WHERE p.partition_id IS NOT NULL AND t.name = 'FactResellerSalesWithPartition'   ORDER BY p.partition_number 
GO

  Results
Results

Partitioned Aligned Index Views

With SQL Server 2005, we could create index views on the partition table but switching out/in partitions was not allowed and we needed to drop and re-create the index views each time of partition switching.

Starting with SQL Server 2008 we can create partitioned aligned index views or basically we can create index views that are partition aware, and with this the materialized index data of index views can be switched out/in without the need of dropping and recreating index views making the maintenance work efficient.

In order to create a partition aligned index view, we need to ensure some requirements, details of which can be found here.

Conclusion

In this article of the series, I demonstrated with a step-by-step guide how to create a partition table/index using the Wizards in SQL Server Management Studio (SSMS) and then talked about Partition Aligned Index Views. In the next article, I am going to talk in detail about loading data in a partitioned table in bulk, data archiving and sliding window scenario.

Resources

Using Partitioned Views

Partitioned Tables and Indexes

Implementing Partitioned Tables and Indexes

Special Guidelines for Partitioned Indexes

See all articles by Arshad Ali

Arshad Ali
Arshad Ali
Arshad Ali works with Microsoft India R&D Pvt Ltd. He has 8+ years of experience, mostly on Microsoft Technologies. Most recently, as a SQL Developer and BI Developer he has been working on a Data Warehousing project. Arshad is an MCSD, MCITP: Business Intelligence, MCITP: Database Developer 2008 and MCITP: Database Administrator 2008 certified and has presented at several technical events including SQL-School. On an educational front, he has an MCA (Master in Computer Applications) and MBA-IT. Disclaimer : I work for Microsoft and help people and businesses make better use of technology to realize their full potential. The opinions mentioned herein are solely mine and do not reflect those of my current employer or previous employers.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles