Table partitioning is quite new to me. I've been studying it for the past week. I was able to create a simple POC. The target table that we are going to apply partitioning to currently has around 100 million records and is partitioned by an foreign key/ client Id that also grows as time goes by. We plan to apply partitioning on it and scatter the filegroups against our server's disk drives.
Now my questions are
Should there be a 1:1 correspondence between number of partitions and number of filegroups?
Will SQL Server automatically determine what filegroup a certain partition goes to? Lets say the table has 20 partitions, and we created one filegroup per drive (5 disks). When I enroll those filegroups against partition scheme/ function, will SQL Server do the allotment itself?
Any advice would be great.
Thanks in advance.