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?

1 Answer 1


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.

  • Alright so then if you create a table in one filegroup and create a clustered index and nonclustered indexes for said table but in another filegroup, exactly what would be in each of the two filegroups?
    – Crattik
    Mar 15, 2020 at 5:26
  • Your table definition and index definition are in system tables within the PRIMARY filegroup. All the data is in the other.
    – Rob Farley
    Mar 15, 2020 at 11:32
  • The table will be "born" in the "one filegroup" (for instance as a heap table if it at that point in time doesn't have a clustered index). And then when you create the clustered index, the data (clustered index) of the table is now built in the "another filegroup) and the old data is removed. I.e., you moved the data from the old to the new filegroup. And, as Rob mentions, meta-data like catalog views are in the primary filegroup. Mar 16, 2020 at 11:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.