If I partition a table, can I replace the file for a partition without telling SQL Server?

Question

I would like to be able to swap one table partition for another, just by replacing partitionN.ndf before starting up the server.

The general aim is to be able to split out some sets of table rows into different files so that when the app is installed, it only goes with one set. There are some rows that are always needed, so

Scenario A

ID     Game         Strategy
1      Squash       Stick to the T
2      Racketball   Drop it at the back
3      Tennis       Serve to the backhand
1000   Croquet      The key is to be really mean
1001   Billiards    Glare a lot

Scenario B

ID     Game         Strategy
1      Squash       Stick to the T
2      Racketball   Drop it at the back
3      Tennis       Serve to the backhand
1000   Baseball     Favour third
1002   Pool         Snooker them, be irritating

Here I would partition out the IDs from 1000, and keep the low numbers in the common database. There will be lots of IDs needing to maintain referential integrity with tables in the common database in the scenario-specific partitions.

Would that work? Or would I need to issue some partitioning command to the server to replace it while the server is running? I suppose part of the question is: does the server just start up and read the files, or does it maintain caches and other things that would be sensitive to the replacement?

Answer 1

I do not think that it will work at all. The File is a far more complex structure than the single table (gam, sgam, pfs, file header pages) as well as the partitioned table has a HoBT ID per partition within the table and your new file will not have the same HoBT ID for the IAM etc.

Edit :

Your example is not the problem that partitioning is designed to solve, you are basically trying to have a table pre-populated with a certain portion of rows that are fixed, and a number of rows that are variable based on an installation criteria.

Personally I suggest you ignore partitioned tables immediately for this, it is not the right tool for the job - you could choose instead to split the values into 2 physical tables and then place a view on top of the two, union'ing the two tables together.

This at least means you are only trying to replace the table, not an individual partition - but I still wouldn't like that approach - if I have enough privledges to post install add and remove filegroups / files, then I would have enough privledges to use a proper data loading routine and just load the data as required.

If you needed physical seperation of the fixed and variable portion of the values, then you can use the view approach afterwards, if required.