Data Partitioning

Partitioning is a techniques where we can divided any database object into multiple parts like Table, Index etc.

What is a table partitioning?

Table Partitioning is a way where very large table divided into multiple parts or files, where data horizontally divided means columns are same but row divided into multiple files group according to partition key.

Partition key:

When data physically divided based on the particular column then this one column called partition key and sql server also distribute data based on that’s column’s value.

Partition elimination:

Keep in mind: always create partition on useful column which one always used as a filter, So Server can access data from the relevant partitions so greatly improve your performance on large tables. This is called partition elimination.

What is a Partition Function?

Partition function specifies boundary of values or making a range of data category based on partition key, and always need to create 1 extra boundary point like we have 5 range point then 5+ 1 ,+1 use for handle upper bound data.

Its have 2  range options

1. Range Left : means that the actual boundary value belongs to its left partition

2.Range Right: means that the actual boundary value belongs to its right partition

What is a Partition Scheme?

Partition scheme create mapping between logical partitions to physical file groups. And It is possible to mapped all partitions to single file group. A file group contains one or more files from the single or multiple Hard Disk.

Steps for Create partition Table

1. Create Database
2. Create File groups
3. Create Files
4. Create partition function
5. Create partition scheme
6. Create table with partition

Step 1. Create Database

CREATE DATABASE MMG_Sales
ON (NAME = MMG_Sales, FILENAME = ‘C:\DATA\MMG_Sales.mdf’, SIZE = 10, FILEGROWTH = 5)
LOG ON (NAME = MMG_Sales_log, FILENAME = ‘C:\DATA\MMG_Sales_log.ldf’,SIZE = 5MB, FILEGROWTH = 5MB);

Step 2. Create File groups

USE MMG_Sales;
GO
ALTER DATABASE MMG_Sales ADD FILEGROUP MMG_Sales_FG_2015;
ALTER DATABASE MMG_Sales ADD FILEGROUP MMG_Sales_FG_2016;
ALTER DATABASE MMG_Sales ADD FILEGROUP MMG_Sales_FG_2017;
ALTER DATABASE MMG_Sales ADD FILEGROUP MMG_Sales_FG_2018;
ALTER DATABASE MMG_Sales ADD FILEGROUP MMG_Sales_FG_2019;

Verify created file groups :-

SELECT name FROM sys.filegroups

Step 3. Create File

ALTER DATABASE MMG_Sales ADD FILE
(NAME = MMG_Sales_F_2015, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MMG_Sales_F_2015.ndf’,
FILEGROWTH = 1 MB ) TO FILEGROUP MMG_Sales_FG_2015;
ALTER DATABASE MMG_Sales ADD FILE
(NAME = MMG_Sales_F_2016, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MMG_Sales_F_2016.ndf’,
FILEGROWTH = 1 MB ) TO FILEGROUP MMG_Sales_FG_2016;
ALTER DATABASE MMG_Sales ADD FILE
(NAME = MMG_Sales_F_2017, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MMG_Sales_F_2017.ndf’,
FILEGROWTH = 1 MB ) TO FILEGROUP MMG_Sales_FG_2017;
ALTER DATABASE MMG_Sales ADD FILE
(NAME = MMG_Sales_F_2018, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MMG_Sales_F_2018.ndf’,
FILEGROWTH = 1 MB ) TO FILEGROUP MMG_Sales_FG_2018;
ALTER DATABASE MMG_Sales ADD FILE
(NAME = MMG_Sales_F_2019, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\MMG_Sales_F_2019.ndf’,
FILEGROWTH = 1 MB ) TO FILEGROUP MMG_Sales_FG_2019 ;

Verify created files

SELECT name,physical_name FROM sys.database_files where type_desc = ‘ROWS’

Step 4. Create partition function

Range

1. Till 2015-12-31 23:59:59
2. 2016-01-01 to 2016-12-31 23:59:59
3. 2017-01-01 to 2017-12-31 23:59:59
4. 2018-01-01 to 2018-12-31 23:59:59
5. 2019-01-01 to 2019-12-31 23:59:59

CREATE PARTITION FUNCTION MMG_Sales_PF_Yrs
AS RANGE RIGHT FOR
VALUES (‘20151231 23:59:59’,
‘20161231 23:59:59’,
‘20171231 23:59:59’,
‘20181231 23:59:59’,
‘20191231 23:59:59’);

Verify created partition function

select * from sys.partition_functions

Verify created partition function Range

select * from sys.partition_range_values

Step 5. Create partition scheme

CREATE PARTITION SCHEME MMG_Sales_PS_Yrs
AS PARTITION MMG_Sales_PF_Yrs
TO (MMG_Sales_FG_2015,
MMG_Sales_FG_2016,
MMG_Sales_FG_2017,
MMG_Sales_FG_2018,
MMG_Sales_FG_2019,
[Primary] );

There one more file groups added for upper boundary storage, like after “2019-12-31 23:59:59” data will store in ‘primary’ file and before ‘2015-12-31 23:59:59’ all records will store into ‘MMG_Sales_FG_2015’.

Verify created partition scheme

select * from sys.partition_schemes

Step 6. Create table with partition Table

CREATE TABLE Voucher (
Voucher_id int identity ,
Voucher_Date date,
tran_time datetime,
Voucher_amount numeric(18,2),
naration varchar(1000))
ON MMG_Sales_PS_Yrs (tran_time) ; — Apply Partition Scheme

Note: Primary key constraints not allow on Partition Table, but index can create like

create clustered index Voucher_Voucher_id on Voucher(Voucher_id);
create nonclustered index Voucher_tran_time on Voucher(tran_time);

Table schema look like

Populating random data into voucher table

Partition/Filegroup wise row count

Check partition with raw data :

select $PARTITION.MMG_Sales_PF_Yrs(tran_time) AS PartitionNumber,* from Voucher ORDER BY voucher_id