SQL Tutorials - 36.Partition Table in SQL

In this Tutorial we will learn how to partition our table which is divided our data into units by using more than one file group in a database.

Benefit : increase query performance.

Script :

--1.Create FileGroup for database

ALTER DataBase [Curriculum] Add FileGroup FileGroup_2012
ALTER DataBase [Curriculum] Add FileGroup FileGroup_2013
ALTER DataBase [Curriculum] Add FileGroup FileGroup_2014
ALTER DataBase [Curriculum] Add FileGroup FileGroup_2015


--2. Create Data files in respective filegroup

Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2012',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2012;
Go

Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2013',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2013;
Go

Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2014',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2014;
Go


Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2015',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2015;
Go



--3. Create Partition Function
Create Partition Function [Section_PartitionFunction] (date)
As Range Right For Values ('2012','2013','2014','2015') ;
Go

--- 4. Create Partition Schema

Create Partition Scheme [Section_PartitionSchema]
As Partition [Section_PartitionFunction] TO ( [PRIMARY],[FileGroup_2012], [FileGroup_2013], [FileGroup_2014], [FileGroup_2015],[PRIMARY])
Go




-- Check if the partition was setup correctly
GO

-- 5. Create clustered index on partition schema and then create coulmnstore index.
CREATE CLUSTERED INDEX [PK_Section]
ON [dbo].[Section](InsertDate)
ON [Section_PartitionSchema](InsertDate)
GO

Select *
From [dbo].[Section]
Where $Partition.[Section_PartitionFunction] (InsertDate) in (3,4);

Last but not the least, SUBSCRIBE to my YouTube channel to stay updated about the regularly uploaded new videos.

[ishusingh]

To be honest, much much better than the other videos having much more views than this one..
Very clearly and explain in easyway.

[HardeepSingh-vhcw]

Great Video, I tried other youtube channels for partitioning data, they are so difficult to follow, miss-steps no clear explanation BUT this video was wonderful for somebody who is going to partition data for the first time, thorough, correct pace, clear instructions, clear explanation. just follow what he says to the letter and voila you have partitioned your data. I am very new to SQL I just found about partitioning data about two days ago and I was able to partition my database on first try with so ease. BIG Thanks

[abelalvarezcruz]

Great video! I have a question, how can I revert the table to its previous state when the table was not partitioned, without having to drop the table? That is, is there any way to remove the partition scheme from the table, so that it allows me to remove that partition scheme from the database and then the function ...

[devakumar]

Hi,
I am trying to do the partition on one of the existing table for the year 2023 on Monthly basis so can you please help me with Partition Function and Partition Scheme queries please. Here I am using uploaddate column as a partition key.

[sanjayschopra]

I have implemented Table Partitioning in Azure Sql Server and my table is working fine but sometimes It takes time to retrieve data like in 1 min it retrieves more than 1 lakh records and sometimes i get only 15000 .Please help me on this