How To Create Partition On SQL Server

 

Partition is the process of dividing tables and indexes into parts.

For example, I have a table with a size of 1 TB.

And this table has a column with datetime type.

You can divide the table by day, month, or year according to this datetime column.

In an example I saw earlier, an application developer created a different table for each month so that the table would not grow too large.

In this way, hundreds of tables are created for the same job in the database.

SQL Server can provide this feature with partition method.

You are using a single table instead of hundreds of tables.

SQL Server divides the values ​​in this table into partitions.

 

What are the benefits of partitioning?

  • Let’s consider that we make the table partition month by month. When we try to reach a third month, we will be able to access the partition faster because we will only access the third month’s partition.
  • If we want to perform maintenance operations such as index rebuild, index reorganize on the table, we can do partition based instead of whole table. This way we will be able to perform this operation more quickly.
  • If two tables are partitioned according to the same column and join these two tables through this column, you will get a significant performance increase. Because partitions can be joined on a partition basis.
  • You can increase performance by activating Lock Escalation on a partition basis instead of all tables. With the help of the following query, we can make lock escalation partition-based on partitioned tables. If the table is not partitioned, the lock escalation will be based on the table.

 

Lock Escalation is the process of converting a large number of small level locks into a smaller number of upper level locks.

For example, many row locks are converted into page locks.

SQL Server does this automatically.

You may want to read the article titled “SQL Server Lock Types“.

When you create a partition, you can put each partition in a different filegroup.

By putting these files on different disks, you can also increase I/O performance.

For more information about the Filegroup, you may want to read the article “How To Create a Database On SQL Server“.

To put partitions into different file groups, we create two file groups with the help of the following script. I create one file in each filegroup.

 

Use the following script to create the partition function and partition schema.

In the partition function, we specify that the dates for January 1 2007 and earlier will be on a partition, that the data from January 1 2007 to January 1 2008 will be on another partition, and the rest will be on another partition.

In the Partition Schema, we also specify filegroups.

 

Use the following script to create a table that uses this partition.

The same partition structure can be used by more than one table.

 

Perform four insert operations to the table with the following script.

 

We can use the following script to query which record is on which partition.

 

How can we partition an existing table?

First Method:

By creating a new table in the partition structure, we can export the data in the current table into this new table.

 

Second Method:

First, we delete the table we created in our example and recreate it without the partition structure.

 

Later I will add a few records to this table as below.

 

With the following script, we can query how many records are in which partition.

 

Then we can make the table partitioned by creating a clustered index on the table with the following script.

 

If there is a clustered index, we should get create script as below.

 

We need to set the drop existing in the script to “ON”, and at the end we have to write the value of “ON RecordTimeScheme(RecordTime)”.

“RecordTimeScheme” is the partition scheme.

“RecordTime” is the name of the partition column.

The final structure of the script should be in the following structure.

 

You can find the sliding window scenario and switch partition issues related to partition in the article titled “Sliding Window-Switch Partition-Split Range-Merge Range“.

To avoid troubles in the switch partition process, all the indexes in the partitioned table must be aligned according to the partition schema.

You may want to take a look at “Can Not Switch The Partition On SQL Server” to see what the alignment process is and how it is done.

You can also use the following articles to see partitioned tables and partition details.

How To Find Partitioned Tables In The Database On SQL Server“,

How To See Partition Details In Partitioned Table On SQL Server