IN this article, we are going to learn how we can manage the filegroup of SQL Database in SQL Server. The SQL Server has four filegroups.

1. Primary Filegroup: The primary filegroup is a default filegroup. When we create a new SQL database, the Primary filegroup is automatically created.
2. Secondary/User-defined filegroups: The secondary filegroup is created by a user. The secondary filegroup is used to manage the data of the database. If you want to keep your highly accessed tables in faster disks, you can create a secondary filegroup and a table in it.
3. Memory-optimized filegroup: The memory-optimized filegroup is used to store the In-memory OLTP tables and table variables. To create a memory-optimized table in a SQL database, we must create a memory-optimized filegroup. We will learn more about the memory-optimized filegroups in the next article.
4. FILESTREAM filegroup: The FILESTREAM filegroup is created to host the FILESTREAM data and FILETABLES. I will explain more about the FILESTRAEAM filegroup in the next article.

The filegroup contains one or multiple data files. This article explains how we can manage the We are going to understand the following use cases.

1. Add data files in filegroups
2. Rename the filegroup
3. Change default filegroup

To demonstrate the above scenarios, I have installed SQL Server 2019 on my laptop and restored a stackoverflow2010 sample database. You can download the Stackoverflow2010 database from here. I have also created a filegroup named FG_POSTS in a database. You can view the list of filegroups and list of datafiles associated filegroup by executing the following query.

Output

Now, let us understand all use cases.

Add data files in a filegroup

We can use ALTER DATABASE ADD FILE TO FILEGRAOUP statement. The syntax is following:

In the syntax,

1. db_name: Specify the name of the database in which you want to add a data file. The db_name must be specified after ALTER DATABASE keyword.
2. logical_file_name: Specify the logical name of the secondary data file.
3. file_location: Specify the path of the data file.
4. file_size: Specify the initial size of the data file. The unit of the Size parameter can be KB/MB/GB.
5. datafile_growth: Specify the growth of the data file. The unit of the FILEGROWTH parameter can be KB/MB/GB
6. file_group_name: Specify the name of the filegroup in which you want to add a data file.

For example, we want to add a data file to the FG_POSTS filegroup with the following options

1. logical file name: fg_posts_data
2. datafile location: D:\FG_Posts
3. data_file_name: fg_posts_data.ndf
4. initial Size: 10MB
5. growth: 5MB.

The query to add a data file is following.

Once the command is executed, successfully open the D:\FG_Posts to view the data file.

As you can see, the file has been created. Alternatively, you can run the following query to view the filegroups.

Query Output

As you can see, the data file named fg_posts_data has been created.

Now, let us understand how we can rename the existing filegroup.

Rename existing filegroup

You can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax:

In the syntax,

1. db_name: Specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement.
2. file_group_name: Specify the filegroup name that you want to rename.
3. file_group_new_name: Specify the new name of the filegroup.

Suppose you want to rename the filegroup named FG_Posts to FG_POSTS_1. The query to rename the filegroup the command is following:

Once the command is executed successfully, run the following query to verify the changes have been applied or not.

Output

As you can see, the filegroup has been renamed successfully.

Now, let us see how we can mark a secondary filegroup as a default filegroup.

Change default filegroup

To change the default filegroup, we can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax:

In the syntax,

1. db_name: Specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement.
2. File_group_name: Specify the filegroup name that you want to make as a default filegroup. The filegroup name must be specified after MODIFY FILEGROUP keyword.
3. DEFAULT: The DEFAULT keyword must be specified after the filegroup name.

Suppose you want to set the FG_POSTS_1 as a default filegroup. To do that, run the following query.

Once the command is executed successfully, run the following query to view the default filegroup of a database named Stackoverflow2010.

Query output

As you can see, the FG_POSTS_1 is a default filegroup.

Now, let us create a table named tblStackoverflow_Users. The script to create the table is the following:

Now, let us see in which filegroup the table has been created. To do that, run the following query:

Output

As you can see, the table is created in the FG_POSTS_1 filegroup.

Summary

In this article, we learned how we could manage the filegroups in SQL Database. We learned the following scenarios:

1. Add data files in filegroups
2. Rename the filegroup
3. Change default filegroup

In my upcoming articles, we will learn how we can add FILESTREAM filegroup and memory-optimized file groups in a SQL Database.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022