SQL Server Large Tables with Data Partitioning.

Partitioning is primarily planned to make managing large tables easier for database administrators. However, partitioned objects can also improve performance of SELECT, UPDATE and DELETE queries.

It is just like one large table made up of multiple little tables. Each chunk, or partition, has the same columns– just a different range of rows.

Prior to SQL Server 2016 SP1, partitioned tables and indexes were not available in every edition of SQL Server. For a list of features that are supported by the editions of SQL Server, see Editions and Supported Features for SQL Server 2016.

 Benefits of Partitioning

Partitioning can provide tremendous benefit to a wide variety of applications by improving manageability, availability and performance. It is not unusual for partitioning to greatly improve the performance of certain queries or maintenance operations. Moreover, partitioning can greatly simplify common administration tasks.

Partitioning can enhance query performance, but there is no guarantee, The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.” And, “In summary, partitioning is mostly for improved maintenance, fast loads, fast deletes and the ability to spread a table across multiple filegroups; it is not primarily for query performance.

Creating a Partitioned Table

To create a partitioned table there are a few steps that need to be done:

  1. Create additional filegroups if you want to spread the partition over multiple filegroups.
  2. Create a Partition Function
  3. Create a Partition Schema
  4. Create the table using the Partition Schema

Step 1  To start with, let’s create a sample database  which contains several filegroups for our demo purposes.

Copy to Clipboard

Step 2
Now let’s create the files for each filegroup.

Copy to Clipboard

Step 3 – Create Partition Function
This creates a range of values for the partition. This will create six partitions:

Copy to Clipboard

Step 4 – Create partition scheme
Then we create our partition scheme.
This creates the partition scheme to determine where each of the partitions will reside.

Copy to Clipboard

Step 5 – Create a new partitioned table
This creates the table using the partition scheme psFiveDayRange that was created in step 3.  The column businessDate is used to determine what data gets placed in which partition/filegroup.

Copy to Clipboard

Step 6
Adding Data to Partitioned Table

After the table has been setup as a partitioned table, when you enter data into the table SQL Server will handle the placement of the data into the correct partition automatically for you.

So, based on the above setup if we run the below commands the data will be placed in the appropriate partition as shown below.

Copy to Clipboard

To determine what exists in each partition you can run the following command:

Copy to Clipboard

Output of above query Something like below:

In addition to determining the number of rows that are in each of the partitions we can also see how fragmented each of these partitions are.  By using the DMV sys.dm_db_index_physical_stats we can get this information.

Copy to Clipboard

Output of above query Something like below:

Based on this results from sys.dm_db_index_physical_stats, you can rebuild an index for a partition.  Here is an example of the code that could be used to rebuild index IDXC_businessDate only on partition #5.

Copy to Clipboard

I hope this gives you an idea of how helpful data partitioning can be to move sets of data and maintain large datasets.  Although this is very simple on the outside there are several things that need to be thought about before implementing a data partitioning scheme.As you can see this is a great enhancement to SQL Server.