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',
FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NOVA12SQLSERVER\MSSQL\DATA\FileGroup_2012.ndf',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2012;
Go
Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2013',
FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NOVA12SQLSERVER\MSSQL\DATA\FileGroup_2013.ndf',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2013;
Go
Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2014',
FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NOVA12SQLSERVER\MSSQL\DATA\FileGroup_2014.ndf',
Size = 80MB,
MaxSize = 2200,
FileGrowth = 1024MB
)
To FileGroup FileGroup_2014;
Go
Alter Database [Curriculum]
ADD File
(
Name = N'FileGroup_2015',
FileName = N'C:\Program Files\Microsoft SQL Server\MSSQL11.NOVA12SQLSERVER\MSSQL\DATA\FileGroup_2015.ndf',
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
SELECT ps.name As [Name of PS], pf.name As [Name of PF], prf.boundary_id, prf.value
FROM sys.partition_schemes ps
INNER JOIN sys.partition_functions pf ON pf.function_id = ps.function_id
INNER JOIN sys.partition_range_values prf ON pf.function_id = prf.function_id
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.
Linkedin : / mohammed-rasool