Table partitioning in SQL Server

Published: 14 November 2025
on channel: Solved Pages

731

--How to create table partitioning in SQL Server

--storing recent 5 years of data
--2019 --FL_2019.NDF -- FG_2019(FILEGROUP) - PARTITION -1 -- If you insert 2019 data or erlier data it will go to partition - 1
--2020 --FL_2020.NDF -- FG_2020(FILEGROUP) - PARTITION -2 -- If you insert 2020 data it will go to partition - 2
--2021 --FL_2021.NDF -- FG_2021(FILEGROUP) - PARTITION -3 -- If you insert 2021 data it will go to partition - 3
--2022 --FL_2022.NDF -- FG_2022(FILEGROUP) - PARTITION -4 -- If you insert 2022 data it will go to partition - 4
--2023 --FL_2023.NDF -- FG_2023(FILEGROUP) - PARTITION -5 -- If you insert 2023 data it will go to partition - 5
--2024 --FL_2024.NDF -- FG_2024(FILEGROUP) - PARTITION -6 -- If you insert 2024 data or any data later 2024 will go to partition -2

--1. Create file groups
--2. create secondary data files adding them to filegroups
--3. Create partition function
--4. Create partition Schema
--5. Create table & inserting data
--6. Validating the inserted data.

USE master;
GO
--Create database
CREATE DATABASE PartitionDemo;
GO

--Adding filegroups
ALTER DATABASE PartitionDemo ADD FILEGROUP FG_2019;
ALTER DATABASE PartitionDemo ADD FILEGROUP FG_2020;
ALTER DATABASE PartitionDemo ADD FILEGROUP FG_2021;
ALTER DATABASE PartitionDemo ADD FILEGROUP FG_2022;
ALTER DATABASE PartitionDemo ADD FILEGROUP FG_2023;
GO

--Adding secondary data files to Filegroups
ALTER DATABASE PartitionDemo
ADD FILE (NAME = 'FG_2019_File', FILENAME = 'C:\SQLData\FL_2019.ndf', SIZE = 5MB) TO FILEGROUP FG_2019;

ALTER DATABASE PartitionDemo
ADD FILE (NAME = 'FG_2020_File', FILENAME = 'C:\SQLData\FL_2020.ndf', SIZE = 5MB) TO FILEGROUP FG_2020;

ALTER DATABASE PartitionDemo
ADD FILE (NAME = 'FG_2021_File', FILENAME = 'C:\SQLData\FL_2021.ndf', SIZE = 5MB) TO FILEGROUP FG_2021;

ALTER DATABASE PartitionDemo
ADD FILE (NAME = 'FG_2022_File', FILENAME = 'C:\SQLData\FL_2022.ndf', SIZE = 5MB) TO FILEGROUP FG_2022;

ALTER DATABASE PartitionDemo
ADD FILE (NAME = 'FG_2023_File', FILENAME = 'C:\SQLData\FL_2023.ndf', SIZE = 5MB) TO FILEGROUP FG_2023;
GO

--create partition function
CREATE PARTITION FUNCTION PF_OrderDate (DATE)
AS RANGE RIGHT FOR VALUES
(
'2019-12-31',
'2020-12-31',
'2021-12-31',
'2022-12-31',
'2023-12-31'
);
GO

--create partition schema
CREATE PARTITION SCHEME PS_OrderDate
AS PARTITION PF_OrderDate
TO
(
FG_2019,
FG_2020,
FG_2021,
FG_2022,
FG_2023,
[PRIMARY] -- For 2024 and above
);
GO

--create partitioned table
CREATE TABLE dbo.Sales
(
SalesID INT IDENTITY(1,1),
OrderDate DATE NOT NULL,
Qty INT,
Amount MONEY
)
ON PS_OrderDate (OrderDate);
GO

--inserting sample data
INSERT INTO dbo.Sales (OrderDate, Qty, Amount)
VALUES
('2018-05-10', 2, 200),
('2019-10-15', 5, 500),
('2020-03-11', 3, 300),
('2021-07-18', 4, 400),
('2022-12-20', 10, 1000),
('2023-04-25', 1, 100),
('2024-01-10', 6, 600);
GO

--Validate inserted data
-- Step 5: Check partition number for each row
SELECT
SalesID,
OrderDate,
Qty,
Amount,
$PARTITION.PF_OrderDate(OrderDate) AS PartitionNumber
FROM dbo.Sales
ORDER BY PartitionNumber, SalesID;

On this page of the site you can watch the video online Table partitioning in SQL Server with a duration of hours minute second in good quality, which was uploaded by the user Solved Pages 14 November 2025, share the link with friends and acquaintances, this video has already been watched 731 times on youtube and it was liked by 16 viewers. Enjoy your viewing!

843