In SQL Server, you can use the FILEGROUP_ID() function to return the ID of a filegroup, based on its name.

To return the filegroup ID, simply pass its name to the function.

Example 1 – Basic Query

Here’s an example to demonstrate.

USE WideWorldImporters;
SELECT FILEGROUP_ID('PRIMARY') AS Result;

Result:

+----------+
| Result   |
|----------|
| 1        |
+----------+

Example 2 – More Filegroups

This database has three filegroups, so here’s an example that outputs all three.

SELECT 
  FILEGROUP_ID('PRIMARY') AS FG_1,
  FILEGROUP_ID('USERDATA') AS FG_2,
  FILEGROUP_ID('WWI_InMemory_Data') AS FG_3,
  FILEGROUP_ID('OOPS') AS FG_4;

Result:

+--------+--------+--------+--------+
| FG_1   | FG_2   | FG_3   | FG_4   |
|--------+--------+--------+--------|
| 1      | 2      | 3      | NULL   |
+--------+--------+--------+--------+

Here, I attempted to output a forth filegroup, but in this case, there’s no filegroup with that name, so I get NULL for that column.

Return Filegroup Name

You can also use FILEGROUP_NAME() to return a filegroup’s name based on its ID.

Return All Filegroups

If you want to return all filegroups for a given database, see How to Return All Filegroups in SQL Server.

Create a Filegroup

If you want to create a new filegroup, see How to Add a Filegroup in SQL Server.