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;