SQL Table Partitioning using SSMS

This section will show you how to create Table Partitioning using SSMS or SQL Server Management Studio. For this demonstration, we will use the table below. This table was created from the Adventure Works DW database. You can use the below query to get the same data.

USE [AdventureWorksDW2014]
GO
SELECT CUST.[FirstName]
      ,CUST.[LastName]
      ,CUST.[EnglishEducation] AS Education
      ,CUST.[EnglishOccupation] AS Occupation
      ,CUST.[YearlyIncome] AS Income
      ,GEO.[City]
      ,GEO.[StateProvinceName] AS State
      ,GEO.[EnglishCountryRegionName] AS Country
      ,GEO.[PostalCode]
	  ,FACT.[OrderQuantity]
      ,FACT.[TotalProductCost] AS TotalCost
      ,FACT.[SalesAmount]
      ,FACT.[TaxAmt]
	  ,FACT.OrderDate
  FROM [DimGeography] AS GEO
  INNER JOIN [DimCustomer] AS CUST
  ON  GEO.[GeographyKey] = CUST.[GeographyKey]
  INNER JOIN [FactInternetSales] AS FACT
  ON CUST.CustomerKey = FACT.CustomerKey

SQL Table Partitioning using SSMS

To create a Table Partitioning in SSMS, please navigate to the table you want to create a partition. Next, right-click on it, select Storage, and then the Create Partition option from the context menu.

Create Partition Option in Object Explorer 2

Selecting the Create Partition option will open a wizard. The first page is a welcome page.

SQL Table Partitioning using SSMS 3

Select a Partition Column

On this page, you have to select the column that you want to use as a partition column. In this example, we are using the Order Date column.

Select Partitioning Column in the Wizard

SQL Table Partitioning using SSMS – Select a Partition Function

Use this page to select the existing partition function or create a new partition function. Let me create a new one by assigning a name called Monthly Sales Function.

SQL Table Partitioning using SSMS 5

Select a Partition Scheme

On this page, you can select the existing partition scheme or create a new one. If you create a theme for a new function, the existing partition scheme selection is greyed out. Let me create a new one by assigning a name called Monthly Sales Scheme.

Table Partitioning using SSMS 6

Let me show you what will happen if you select the Existing partition function called Monthly partition. This partition was previously created using Query.

Now you can see it is allowing you to select the existing scheme. Or you can create a new scheme.

Choose Existing Partition Scheme

Let me undo those two steps and select New function and New Scheme.

SQL Table Partitioning using SSMS – Map Partition

You can use either the Left boundary or the right boundary.

Table Map Partition

Use the drop-down box to select the Month names. Please use the Set boundaries button to choose the from date and to date, and Estimate Storage to estimate the required disk space.

Map Partitions Right Boundary option

Don’t forget to select the PRIMARY filegroup (default one). Otherwise, it will throw an error.

Map your partitions with Range Boundaries

Table Partition – Select an Output Option

Either you can

  • Create a Script and run it later.
  • Run immediately- This will run this table partition script straight away
  • Schedule: You can use this script to run.

Select the Run Immediately option.

Click the Finish button to create a partition.

Table Partitioning Wizard Review Summary

We have successfully created a table partition using a wizard.

Categories SQL