Blog Post

SQL Server Table Partition Example


Why Use a SQL Server Table Partition

Data warehouses and data repositories often have tables that store millions of records and each day adding thousands more.  As the data ages, the older records generally require fewer updates.  If all of these records are kept in a single physical table.  Queries and maintenance on the table and indexes will take longer to execute.  One alternative to keeping all of the data together is to split the table into smaller units.  SQL Server allows you to partition tables into smaller files and filegroups to make management and access more efficient for your large datasets.  For instance if you are collecting 1 million records daily.  You could create a partition for each month based on the transaction date by creating 12 filegroups to store the monthly partitions.  In this scenario, you would have a single logical entity that represents the entire table.  However, it is physically divided into 12 files in the database.  The applications that perform CRUD operations continue to operate normally and do not require code changes.  However, on the database you would need to configure the table to be a partitioned table.  SQL Server 2014 support up to 15,000 partitions of a table.
Table Partitions can have the following benefits.
  • Data access is more efficient and quicker, do to the volume of records
  • Maintenance operations can run on one or more partitions.  For example, rebuilding an index on a table that is frequently update
In order to create a table partition you need to create a partition function, partition schema, and appropriate files to store the partitions.
These are the steps and scripts required to create a partition on the Sales.SalesOrderHeader table in the AdventureWorks database.

Partition Function

First create a partition function to sort the data based on a datetime value.  In this example, I am going to use the OrderDate on table Sales.SalesOrderHeader to determine which partition to place the data in.

CREATE PARTITION FUNCTION [pf_SalesOrdHeader_byOrderDate](datetime ) AS RANGE RIGHT FOR VALUES (N’2005-01-01T00:00:00′, N’2006-01-01T00:00:00′, 

N’2007-01-01T00:00:00′, N’2008-01-01T00:00:00′, N’2009-01-01T00:00:00′)

Partition Schema

I have created 3 filegroups in the AdventureWorks database that each have a new datafile.  I want to map my data from 2005 to 2009 into these 3 file groups.  To do this, I create a partition scheme to map the dates from the partition function to the file groups in my database.  SalesHeader_fg1 will store data for years <= 2005 and <2006.  SalesHeader_fg2 will store data for years >=2006 and <2008. SalesHeader_fg3 will store data >=2008.

CREATE PARTITION SCHEME [ps_SalesOrdHeader_byYear] AS PARTITION [pf_SalesOrdHeader_byOrderDate]

TO ([SalesHeader_fg1] , [SalesHeader_fg1] , [SalesHeader_fg2] , [SalesHeader_fg2] , [SalesHeader_fg3], [SalesHeader_fg3])

Partition Storage

Now that the partition function and partition schema are created, I need to modify the Sales.SalesOrderHeader table to leverage the newly created partition.  In SSMS right click on the Sales.SalesOrderHeader to configure the storage for this table.  Follow the steps to setup the partitioning and implement it on the table.

Table Partition SQL Server

This script will modify the table to leverage the partitioning:

CREATE CLUSTERED INDEX [ClusteredIndex_on_ps_SalesOrdHeader_byYear_635501670010470225] ON [Sales].[SalesOrderHeader]



)WITH ( SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF , ONLINE = OFF) ON [ps_SalesOrdHeader_byYear]([OrderDate] )

After configuring the partition using the above settings you can look at the table properties to see how the partition is setup.

Partitioned Table Properties

In order to see how the partitioning split the records across the files and filegroups you can run this query:

SELECT OBJECT_SCHEMA_NAME (t. object_id) AS schema_name        

    ,t. name AS table_name        
    ,i. index_id        
    ,i. name AS index_name        
    ,p. partition_number        
    ,p. rows          
FROM sys .tables t             
join sys .indexes i ON t. object_id = i. object_id 
             join sys .partitions p ON i. object_id=p .object_id AND i.index_id =p. index_id 


 For more information on SQL Server Partitions see these links:


The post SQL Server Table Partition Example appeared first on Derek E Wilson - Blog.


You rated this post out of 5. Change rating




You rated this post out of 5. Change rating