Sql-server – One filegroup, multiple datafiles, how to get list of tables in each file

If you're going with traditional partitioned views with each table containing a different yearly quarter, then you have a few levels of separation here:

1. Each Table
2. Each Index on Each Table

So in other words, say you have 10 tables that the partitioned view accesses, and you have a clustered index and a nonclustered index on each table. In this case you are looking at 20 different data structures that you need to place accordingly.

As to where to put each of these will largely depend on your workload. For instance, if only the most recently two quarter tables get the most report queries and data modification, you'll want to ensure those are separate. But in the same respect, you could benefit from each of those tables having their indexes on separate disks.

I say disks here because filegroups are logical groupings of database data files. Even if you split each table and each index on their own filegroup (exaggerated to illustrate a point) but all the containing data files are on the same set of physical disks then you won't really see any underlying I/O performance gain. It's really just administrative at that point. You can't always divide the physical layer and the logical layer when squeezing performance out of a design.

So you need to visually lay out the physical disk separation, what type of common workload you will be receiving, and design your data placement in that manner.

You should assume, for all intents and purposes, that parts of every object in a given filegroup will be distributed across all of the files in that filegroup. You might be able to work up some script that will prove one or two smaller tables don't have any data on file 5 or file 8, but for what purpose? I say it's easier to just write a script that will rebuild all of the tables in that filegroup.