The following partition defines the monthly ranges for one year ahead:
CREATE PARTITION FUNCTION PF_Monthly(date) AS RANGE RIGHT FOR VALUES ( '2022-01-01' , '2022-02-01' , '2022-03-01' , '2022-04-01' , '2022-05-01' , '2022-06-01' , '2022-07-01' , '2022-08-01' , '2022-09-01' , '2022-10-01' , '2022-11-01' , '2022-12-01' , '2013-01-01' )
I am just wondering if its practical to create ranges even further ahead like list them for 20 years ahead? That means a lot of empty partitions, since i plan to store 1 year of recent data in the db.
Is there performance penalty to have that much empty partitions lingering around?
Does SQL server will do more work when it comes to query performance?
Or is it more practical to just define few monthly ranges ahead, and implement an regular range splitting/merging, like a moving window?