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
Right click on the table in the Object Explorer of SSMS and click on Storage -> Create Partition menu item as shown below:
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
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
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
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
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
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
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
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
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
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
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
Partitioned Tables and Indexes
Implementing Partitioned Tables and Indexes
