Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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?
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.
PRIMARY filegroup. All the data is in the other.
Mar 15, 2020 at 11:32