Partitioning Large MS SQL-Server FACT table
• Data should be retained for 5 years or 60 months.
• Table will have about 800 million records
• Have adapted monthly partition.
• Each File group have 8 files to match the Number of CPU improve parallelism.
• Each partition will be kept in one File group.
• The above scenario requires 62 file groups need to be created (60 for partition and two for window sliding)
Do I need to create 62 file groups and required data files using DDLs or any better way of handling this situation? Any ideas or scripts would be great?