Table partitioning in SQL Server
--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;
На этой странице сайта вы можете посмотреть видео онлайн Table partitioning in SQL Server длительностью часов минут секунд в хорошем качестве, которое загрузил пользователь Solved Pages 14 Ноябрь 2025, поделитесь ссылкой с друзьями и знакомыми, на youtube это видео уже посмотрели 731 раз и оно понравилось 16 зрителям. Приятного просмотра!
