I'm creating a database in which there will be around 30 tables, with every table containing tens of millions of rows and each table containing a single important column and a primary/foreign key column in order to maximise query efficiency in the face of heavy updates and insertions and make heavy use of clustered indexes. Two of the tables will contain variable-length textual data, with one of them containing hundreds of millions of rows but the rest will contain only numeric data.
As I really want to squeeze every last drop of performance out of the hardware I have available (about 64GB of RAM, a very fast SSD and 16 cores), I was thinking of allowing each table to have its own file so that no matter if I'm joining on 2, 3, 4, 5 or more tables, each table will always be read using a separate thread and the structure of each file will be closely aligned with the table contents, which would hopefully minimise fragmentation and make it faster for SQL Server to add to the contents of any given table.
One caveat, I'm stuck on SQL Server 2008 R2 Web Edition. Which means I can't use automatic horizontal partitioning, which rules that out as a performance enhancement.
Will using one file per table actually maximise performance, or am I overlooking built-in SQL Server engine characteristics that would make doing so redundant?
Second, if using one file per table is advantageous, why does
create table only give me the option to allocate the table to a file group and not to a specific logical file? This would require me to create a separate file group for every file in my scenario, which suggests to me that perhaps SQL Server isn't envisioning the advantages I am assuming would come from doing what I'm proposing.