At a minimum, a SQL Server database has two operating system files; the data file, and the log file.

The data file holds the actual data in the database, as well as objects such as tables, indexes, stored procedures, and views. The log file contains the information that is required to recover all transactions in the database.

When you create a database using the default options, both of these files are created. These files are created in the primary filegroup. This filegroup contains the primary data file and any secondary files that are not put into other filegroups. So the primary filegroup is the default filegroup (unless it’s changed by using the ALTER DATABASE statement).

When you create multiple data files, you also have the option of grouping them into a user-defined filegroup. This allows you to group files logically into their own filegroup that you created. This filegroup will then exist in addition to the primary filegroup. Database objects will remain will remain within the primary filegroup.

This article demonstrates how to create a user-defined filegroup and add some data files to it.

Example

Here’s an example of using T-SQL to add a filegroup to an existing database:

USE master
GO

ALTER DATABASE Solutions  
ADD FILEGROUP Solutions1Filegroup1;  
GO  
ALTER DATABASE Solutions   
ADD FILE   
(  
    NAME = Solutions_dat_2,  
    FILENAME = 'D:\mssql\data\Solutions_dat2.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5%  
),  
(  
    NAME = Solutions_dat_3,  
    FILENAME = 'D:\mssql\data\Solutions_dat3.ndf',  
    SIZE = 10MB,  
    MAXSIZE = 100MB,  
    FILEGROWTH = 5% 
)  
TO FILEGROUP Solutions1Filegroup1;  
GO

This example creates a filegroup called Solutions1Filegroup1 and adds two data files to it. We specify the usual things for each file, such as its logical name, physical file path, size, etc.

The assumption with this example is that there’s already a data file called Solutions_dat_1. Therefore we name these files the same, but just increment the number for each file.

Also, in this example we name the filegroup as though it’s just one of many. We can create multiple filegroups if required, and if we do, we’ll probably want a naming convention that makes it easy to distinguish between each filegroup.

View the Filegroups

You can view the filegroups for a given database by querying the sys.filegroups catalog view.

Example:

USE Solutions;
SELECT name, type_desc
FROM sys.filegroups;

Result:

name                  type_desc     
--------------------  --------------
PRIMARY               ROWS_FILEGROUP
Solutions1Filegroup1  ROWS_FILEGROUP

You can also use an asterisk (*) to return all rows if required.