If we work with large databases, definitely we need to partition large database tables. Depending on the requirement, we can partition data on yearly, monthly or daily basics. In this example, tables are partitions based on the below scenario.
- Data is partitioned based on the partition granularity, whether Daily or Monthly
- For each year, separate file group was created.
- If data is older than the prior year, those are partitioned on yearly basics for both the daily and monthly partitions.
- For monthly partitioning, data within the current year and the prior year are partitioned on monthly basics.
- For daily partitions, if data is older than 3 months, then those are partitioned on monthly basics, else data is partitioned on daily basics.
To partition data on the above criteria, stored procedure was implemented as below. It was implemented to dynamically create partitions for any table on any database. Also it is possible to provide any column with the date key in the 'YYYYMMDD' format as the PartitionKey. Also have to provide the file storage location. Partition granularity is provided as 'D' for Daily partitions and as 'M' for monthly partitions.
Hope this will be helpful.