Filegroup in SQL Server is a logical container that is used to group together database data files for the purpose of administration and data allocation. There is a default Primary filegroup with every SQL Server database. Filegroups are not the same as data files. Multiple data files can be part of a single file group, and one data file can belong to only one filegroup.
Types of filegroup in SQL Server
There are four types of filegroups in SQL Server namely Primary filegroup, Memory optimized data file group, filestream file group, and user-defined file group. Let’s discuss each one by one.
Primary Filegroup
The primary filegroup is the default filegroup created with the SQL Server database. All the system objects reside in this filegroup. When we create database objects and do not specify the filegroup name, they are assigned to the default primary filegroup. Only one filegroup can be the default filegroup at a time. You can make the other filegroup default by using ALTER DATABASE statement.
Memory-optimized data filegroup
The purpose of the Memory-optimized data filegroup is to hold the In-memory table variables and table. Memory-optimized data filegroup is a prerequisite for creating In-memory tables in the SQL server. We can only create one memory-optimized filegroup in a single database, that will be used to hold only memory-optimized data.
Filestream filegroup
The purpose of the Filestream filegroup is to store the Filestream data and filetables. Filestream filegroups are a little different than other filegroups. This filegroup does not contain the filestream files instead it contains the file system directories, which are called data containers. These Data containers work as a bridge between file system storage and database engine storage.
User-defined/Secondary filegroups
User-defined filegroup or Secondary filegroup is created by the user. It can be created during the database creation or after the database is created. It is used to efficiently manage the database data and also to improve performance. Secondary data files in this group are created with .ndf extension. Multiple secondary data files spreading across multiple disks can be part of this filegroup to improve performance.
How to create filegroup in SQL Server
You can create filegroup using T-SQL and SQL Server management studio (SSMS). Below mention is the T-SQL query to create filegroup.
USE [master]
GO
ALTER DATABASE [SQL_KDB]
ADD FILEGROUP [New_Filegroup_Name]
GO
To create filegroup in SQL Server using SSMS GUI, follow the steps mention below.
- Select the database that you want to add filegroup to, Right-Click on that database, and click properties
- Click on the Filegroups option in the left pane
- On the right pane, Click on Add Filegroup button
- A new row will be inserted, Enter the new filegroup name and click the OK button to save the changes
How to check Filegroup in SQL Server
To check the size of filegroup in SQL Server or to check other details of the Filegroup, you can use the below-mentioned query. It will return the filegroup details like the name, type, and size of the filegroup, the location of the associated data file, and some other details.
SELECT [dbfile].NAME AS [FileName],
[fg].NAME AS [FileGroup_Name],
[fg].type_desc AS [Filegroup Type],
physical_name [DB File Location],
size / 128 AS [Size_MB],
state_desc [FILE State],
growth [DB file growth]
FROM sys.filegroups [fg]
INNER JOIN sys.database_files [dbfile]
ON [dbfile].data_space_id = [fg].data_space_id
Conclusion
In this article, we discuss in general the filegroups in SQL Server, the types of filegroups, how to create filegroup, and how to check its details. In most cases, the Primary default filegroup is used and no other additional secondary filegroups are needed. But it depends on the requirement, if needed SQL DBAs can create other types of filegroups to take advantage of the benefits it comes with.