Sql-server – Indexes all Exist in non Primary Filegroup – Advantages

database-designfilegroupssql-server-2016

I have come across a database where all the indexes are stored on a filegroup that is not the primary filegroup.

The database has 4 files, one of which is in the primary filegroup and the other 3 are in another filegroup, let's call it FileGroupB.

All the indexes are in FileGroupB according to this query:

SELECT  i.name,
        i.type_desc,
        o.name,
        f.name,
        o.type_desc
FROM    [sys].[indexes] i
        JOIN [sys].[filegroups] f
            ON f.[data_space_id] = i.[data_space_id]
        JOIN sys.objects o
            ON o.object_id = i.object_id
WHERE   o.type_desc = 'USER_TABLE'

If I am correct, that would mean all code definitions (EG stored procs, functions etc) are stored in the primary file but all the data is in the secondary filegroup.

What is the benefit of / thought behind this approach?

Best Answer

Not sure if I can list any benefits but that was fashionable about 15 to 20 years ago.

Well actually the db is accessible once the Primary Filegroup is online (if you have enterprise edition) - so you might be back online faster (albeit without any access to your data).

Related Question