Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 | ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO) |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | USE Test ALTER DATABASE Test ADD FILEGROUP FileGroup1 ALTER DATABASE Test ADD FILEGROUP FileGroup2 ALTER DATABASE Test ADD FILEGROUP FileGroup3 ALTER DATABASE Test ADD FILE ( NAME = DataFile1, FILENAME = 'C:\MSSQL\TestDataFile1.ndf') TO FILEGROUP FileGroup1 ALTER DATABASE Test ADD FILE ( NAME = DataFile2, FILENAME = 'C:\MSSQL\TestDataFile2.ndf') TO FILEGROUP FileGroup2 ALTER DATABASE Test ADD FILE ( NAME = DataFile3, FILENAME = 'C:\MSSQL\TestDataFile3.ndf') TO FILEGROUP FileGroup3 |
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.
1 2 | CREATE PARTITION FUNCTION RecordTimePartition (DATETIME) AS RANGE RIGHT FOR VALUES ('20070101',20080101') |
1 2 | CREATE PARTITION SCHEME RecordTimeScheme AS PARTITION RecordTimePartition TO (FileGroup1, FileGroup2,FileGroup3) |
Use the following script to create a table that uses this partition.
The same partition structure can be used by more than one table.
1 2 3 4 5 6 7 | CREATE TABLE [dbo].[PartitionExample]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](250) NULL, [RecordTime] [datetime] NULL ) ON RecordTimeScheme (RecordTime) ALTER TABLE dbo.PartitionExample SET (LOCK_ESCALATION = TABLE) |
Perform four insert operations to the table with the following script.
1 2 3 4 5 6 7 8 9 10 11 | INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Nurullah ÇAKIR','20080201') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Faruk ERDEM', '20070101') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Hakan GURBASLAR','20090101') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES (DILARA AYDIN','20040401') |
We can use the following script to query which record is on which partition.
1 2 3 4 | SELECT Name,RecordTime, $PARTITION.RecordTimePartition(RecordTime) PARTITION FROM dbo.PartitionExample ORDER BY RecordTime |
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.
1 2 3 4 5 6 7 | USE [Test] GO CREATE TABLE [dbo].[PartitionExample]( [ID] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](250) NULL, [RecordTime] [datetime] NULL ) ON [PRIMARY] |
Later I will add a few records to this table as below.
1 2 3 4 5 6 7 8 | INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Nurullah ÇAKIR','20080201') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Faruk ERDEM', '20070101') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES ('Hakan GURBASLAR,'20090101') INSERT INTO dbo.PartitionExample(Name,RecordTime) VALUES (DILARA AYDIN','20040401') |
With the following script, we can query how many records are in which partition.
1 2 3 | SELECT partition_id, object_id, partition_number, rows FROM sys.partitions WHERE object_id = OBJECT_ID('PartitionExample') |
Then we can make the table partitioned by creating a clustered index on the table with the following script.
1 2 3 4 5 6 7 8 | USE [Test] GO CREATE CLUSTERED INDEX [CIX] ON [dbo].[PartitionExample] ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON RecordTimeScheme (RecordTime) |
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.
1 2 3 4 5 6 7 8 | USE [Test] GO CREATE CLUSTERED INDEX [CIX] ON [dbo].[PartitionExample] ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON RecordTimeSchema(RecordTime) |
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”
