I'm studying filegroups at the moment as I haven't used them much before. A class I'm taking mentions the above scenario. I was wondering if I'm understanding it correctly:
Would the 2 filegroups with tables just be holding table objects and the 1 filegroup with indexes be holding the table data?
Best Answer
It depends on what you mean by an index, and what you mean by a table object, because every table is either a heap or a clustered index. Either type of table can also have non-clustered indexes as well, which are copies of the data.
If you have all the non-clustered indexes in a separate filegroup, then you're in a space that was described by Greg Linwood in chapter 33 of the first SQL MVP Deep Dives book, where the suggestion was that because it's only a copy of data you might not have to back it up. It's very complicated to recover from though, so it's not for everyone.
Typically when I've heard people suggesting that all indexes are stored separately, it's because they figure that data that has a lot of Scans run against it might suit a different type of storage to data that has a lot of Seeks (I'm capitalising as I'm referring to the type of database operation, not a general 'scan'). And they figure that indexes have Seeks, and heaps have Scans. But they're forgetting that any kind of lookup (including RID Lookups against heaps) are the same kind of operation as a Seek, and that indexes often have Scans run against them too.