<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=931099456970389&amp;ev=PageView&amp;noscript=1">

Table Partitioning in SQL Server – The Basics

Posted Apr 21, 2015 by Cathrine Wilhelmsen

SQL Server Table Partitioning Cheat SheetThere are many benefits of partitioning large tables. You can speed up loading and archiving of data, you can perform maintenance operations on individual partitions instead of the whole table, and you may be able to improve query performance. However, implementing table partitioning is not a trivial task and you need a good understanding of how it works to implement and use it correctly.Table Partitioning is an Enterprise Edition feature only.

Being a business intelligence and data warehouse developer, not a DBA, it took me a while to understand table partitioning. I had to read a lot, get plenty of hands-on experience and make some mistakes along the way. (The illustration to the left is my Table Partitioning Cheat Sheet.) One of my favorite ways to learn something is to figure out how to explain it to others, so I recently did a webinar about table partitioning. I wanted to follow that up with focused blog posts that included answers to questions I received during the webinar. This post covers the basics of partitioned tables, partition columns, partition functions and partition schemes.

What is Table Partitioning?


Partitioned TableTable partitioning is a way to divide a large table into smaller, more manageable parts without having to create separate tables for each part. Data in a partitioned table is physically stored in groups of rows called partitions and each partition can be accessed and maintained separately. Partitioning is not visible to end users, a partitioned table behaves like one logical table when queried.

This example illustration is used throughout this blog post to explain basic concepts. The table contains data from every day in 2012, 2013, 2014 and 2015, and there is one partition per year. To simplify the example, only the first and last day in each year is shown.

An alternative to partitioned tables (for those who don’t have Enterprise Edition) is to create separate tables for each group of rows, union the tables in a view and then query the view instead of the tables. This is called a partitioned view. (Partitioned views are not covered in this blog post.)

What is a Partition Column?


Partition Column (Partition Key)Data in a partitioned table is partitioned based on a single column, the partition column, often called the partition key. Only one column can be used as the partition column, but it is possible to use a computed column.

In the example illustration the date column is used as the partition column. SQL Server places rows in the correct partition based on the values in the date column. All rows with dates before or in 2012 are placed in the first partition, all rows with dates in 2013 are placed in the second partition, all rows with dates in 2014 are placed in the third partition, and all rows with dates in 2015 or after are placed in the fourth partition. If the partition column value is NULL, the rows are placed in the first partition.

It is important to select a partition column that is almost always used as a filter in queries. When the partition column is used as a filter in queries, SQL Server can access only the relevant partitions. This is called partition elimination and can greatly improve performance when querying large tables.

What is a Partition Function?


Partition FunctionThe partition function defines how to partition data based on the partition column. The partition function does not explicitly define the partitions and which rows are placed in each partition. Instead, the partition function specifies boundary values, the points between partitions. The total number of partitions is always the total number of boundary values + 1.

In the example illustration there are three boundary values. The first boundary value is between 2012 and 2013, the second boundary value is between 2013 and 2014, and the third boundary value is between 2014 and 2015. The three boundary values create four partitions. (The first partition also includes all rows with dates before 2012 and the last partition also includes all rows after 2015, but the example is kept simple with only four years for now.)

But what are the actual boundary values used in the example? How do you know which date values are the points between two years? Is it December 31st or January 1st? The answer is that it can actually be either December 31st or January 1st, it depends on whether you use a range left or a range right partition function.

RANGE LEFT AND RANGE RIGHT

Partition functions are created as either range left or range right to specify whether the boundary values belong to their left or right partitions:

• Range left means that the actual boundary value belongs to its left partition, it is the last value in the left partition.
• Range right means that the actual boundary value belongs to its right partition, it is the first value in the right partition.

Left and right partitions make more sense if the table is rotated:

Partition Function Range Left and Range Right  Partition Function Range Left and Range Right  Partition Function Range Left and Range Right

RANGE LEFT AND RANGE RIGHT USING DATES

The first boundary value is between 2012 and 2013. This can be created in two ways, either by specifying a range left partition function with December 31st as the boundary value, or as a range right partition function with January 1st as the boundary value:

Partition Function Range Left and Range Right  Partition Function Range Left and Range Right  Partition Function Range Left and Range Right

Partition functions are created as either range left or range right, it is not possible to combine both in the same partition function. In a range left partition function, all boundary values are upper boundaries, they are the last values in the partitions. If you partition by year, you use December 31st. If you partition by month, you use January 31st, February 28th / 29th, March 31st, April 30th and so on. In a range right partition function, all boundary values are lower boundaries, they are the first values in the partitions. If you partition by year, you use January 1st. If you partition by month, you use January 1st, February 1st, March 1st, April 1st and so on:

Partition Function Range Left and Range Right  Partition Function Range Left and Range Right

RANGE LEFT AND RANGE RIGHT USING THE WRONG DATES

If the wrong dates are used as boundary values, the partitions incorrectly span two time periods:

Partition Function Range Left and Range Right  Partition Function Range Left and Range Right

What is a Partition Scheme?


Partition SchemeThe partition scheme maps the logical partitions to physical filegroups. It is possible to map each partition to its own filegroup or all partitions to one filegroup.

A filegroup contains one or more data files that can be spread on one or more disks. Filegroups can be set to read-only, and filegroups can be backed up and restored individually. There are many benefits of mapping each partition to its own filegroup. Less frequently accessed data can be placed on slower disks and more frequently accessed data can be placed on faster disks. Historical, unchanging data can be set to read-only and then be excluded from regular backups. If data needs to be restored it is possible to restore the partitions with the most critical data first.

How do I create a Partitioned Table?


The following script (for SQL Server 2012 and higher) first creates a tally table helper function created by Itzik Ben-Gan that is used to insert test data. The script then creates a partition function, a partition scheme and a partitioned table. (It is important to notice that this script is meant to demonstrate the basic concepts of table partitioning, it does not create any indexes or constraints and it maps all partitions to the [PRIMARY] filegroup. This script is not meant to be used in a real-world project.) Finally it inserts test data and shows information about the partitioned table.

Summary

SQL Server Table Partitioning Cheat SheetThe partition function defines how to partition a table based on the values in the partition column. The partitioned table is created on the partition scheme that uses the partition function to map the logical partitions to physical filegroups.

If each partition is mapped to a separate filegroup, partitions can be placed on slower or faster disks based on how frequently they are accessed, historical partitions can be set to read-only, and partitions can be backed up and restored individually based on how critical the data is.

This post is the first in a series of Table Partitioning in SQL Server blog posts. It covers the basics of partitioned tables, partition columns, partition functions and partition schemes. Future blog posts in this series will build upon this information and these examples to explain other and more advanced concepts.

Want to learn more from Cathrine? Check out her blog or follow her on Twitter!

Tagged With: SQL Server


Blog Comments

Need help with this topic? Ask the author below.

 

Subscribe to Blog Updates