SQL Server Database Files Groups

This article describes SQL Server database file Groups. When we create a new database, then the Server database contains default file Groups. They are called primary files Groups. Database objects and files can be grouped together in file groups for allocation and administration purposes. We can create 32,767 files per database and 32,767 file Groups per database. Databases can be as large as 16 terabytes.

If you want to learn more detail of data files then read my previous article SQL Server Database .Files.

Basically SQL Server allows the following two types of database files Groups.

Primary

It holds the primary data file. A file or file group cannot be used by more than one database. A file can be a member of only one file group. The important thing is that Transaction log files are never part of any file group.

User-defined

A User-defined file group is created by the user when the user first creates or later modifies the database. in which no file can be a member of more than one file group and log space is managed individually from the data space. When a table or index is created without specifying a file group, it will be allocated from the default file group, in other words primary file groups.

How to create database files Groups

When we create a database, then the SQL Server database contains default file Groups. It's called primary files Groups.

You can create additional data file Groups, in other words secondary data files groups.

Right-click on the database name.

Select "Properties" > "Files Groups".

We can see file groups in the following.

Now we will add file groups in the existing database; click the "Add" button.

Now we can provide a file name as in the following screen.

Then click the "OK" button.

New data file groups have been created; we can check using the following query.

USE [Testing]

GO

SELECT * FROM sys.filegroups
 

If we want to add a new file then we can choose file groups.

Click "Ok". Now a new file is created in the new file group as in the following:

New data files and file groups have been created; we can check using the following query.

SELECT f.name,

       g.name,
       RIGHT(physical_name, 4) AS File_DataType,
       physical_name
FROM   sys.master_files f
       INNER JOIN sys.filegroups g
         ON f.data_space_id = g.data_space_id
WHERE  f.name LIKE 'testing%'

 

An Administrator can create file groups for each disk drive and then assign specific tables, indexes, text, ntext, and image data associated with the file group.