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

  1. Should there be a 1:1 correspondence between number of partitions and number of filegroups?

  2. 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.

migrated from stackoverflow.com Aug 2 '16 at 9:14

This question came from our site for professional and enthusiast programmers.

  • If there was a "universal truth" that there should be a 1-1 mapping between two features, why would they exist as two separate features? – Damien_The_Unbeliever Jul 16 '16 at 15:59
  • Is your objective to place all rows for each client_id in a separate filegroup? You need to specify why you want to do that to answer question #1. For question #2, the partition scheme NEXT USED specification determines the filegroup used when new partitions are created when the associated PARTTION FUNCTION is split. – Dan Guzman Jul 16 '16 at 16:38
  • @Dan, yes, we woild like to put specifi client data in their separate file groups, but there is a hard limit of 15ooo partitions and it is possible for us to reach more than 15ooo clients. – user1465073 Jul 17 '16 at 1:28
  • @dan regarding #2 found out what u were talking about sqlservercentral.com/Forums/Topic1630633-2799-1.aspx thanks a many! :D – user1465073 Jul 17 '16 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.