I am looking at partitioning some tables within a database based on an identifier within the tables. Looking at one table in isolation that contains Transactions I want to partition it based on blocks of customers. e.g. from ID 0 to 5000, 5001 to 10000, 10001 to 15000, etc. I have created a FileGroup called TransactionData and have partitioned everything into 20 ranges with the last partition containing anything outside the ranges. This appears to have moved data out of the .mdf file into the new .ndf file for the transactions.
My question is: Would it be better to have multiple .ndf files for each partition or is it fine to have just one containing all partitions relating to transactions?
At the moment its configured as:
TransactionData (FileGroup) = Partition1, Partition2, Partition3, Partition4, etc
Is there any advantage to this:
TransactionData = Partition1
TransactionData1 = Partition2
TransactionData2 = Partition3
TransactionData3 = Partition4
Is there any IO benefit or Loss with SQL in having more .ndf files containing smaller volumes of partitioned data?
The transaction data in total is around 10GB if that helps.