Using Multiple Filegroups for a Database and Changing the Default Filegroup

By: Atif Shehzad   |   Updated: 2012-04-06   |   Comments (2)   |   Related: More > Database Administration

Problem

SQL Server allows you to create additional filegroups to spread storage of data and indexes to different disks to improve I/O performance.  The issue is that when creating new data storage objects we are required to explicitly point to the new filegroup. Is there any option to use the new filegroup by default without explicitly specifying the filegroup when creating new tables and indexes?

Solution

Before we get started, let me explain some terminology:

• Database - is made up of multiple files for database objects and transaction logs
• File - a physical file that is used to store database objects or transaction logs
• Filegroup - this is a container that is made up of one or more files to store database objects
• PRIMARY filegroup - this is the default filegroup that is created when you create a new database.
• Default filegroup - when creating new tables and indexes this specifies which filegroup is the default if a filegroup is not specified

So based on this when creating a new database, the primary file group of a database is the default filegroup and the primary data file is created in it. When a table or index is created without specifying a filegroup, all pages will be allocated from the default filegroup. Note: only one filegroup at a time can be the default filegroup.

By default, the primary filegroup is the default filegroup, so all new objects are placed in this filegroup. However we can change the default filegroup to any other filegroup at any time. When you have multiple filegroups, changing the default filegroup from PRIMARY to another one is a good idea, because it eliminates the need to specify the filegroup when objects are created and makes sure that new objects are created in the correct filegroup.

Example

Let's analyze the problem for which we are required to change the default filegroup from primary to something else.

The following script creates a database and then adds additional filegroups. Although additional filegroups can be created when creating a database, I added the filegroups later for simplicity and illustration. So in this case, the primary filegroup will be the default filegroup.

-- Script 1: Create DB and add additional file group
-- If DB pre exists then drop it
IF exists (SELECT * FROM sys.databases WHERE NAME = 'MSSQLTip')
USE MASTER
DROP DATABASE MSSQLTip
GO
-- Create new DB
CREATE DATABASE MSSQLTip
GO
-- Add file groups to DB
ALTER DATABASE MSSQLTip ADD FILEGROUP FG1
ALTER DATABASE MSSQLTip ADD FILEGROUP FG2
GO
-- Verify file groups in DB
USE MSSQLTip
GO 
SELECT groupName AS FileGroupName FROM sysfilegroups
GO

At this point we have three file groups primary, FG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use.  If we want to create a data storage object in FG1 or FG2 then the only way is to specify the filegroup when creating the storage object.

Consider the following scenario where we want to create a table without specifying the filegroup. It is essential to provide explicit instructions for creating the data storage objects in other than the default filegroup. The only way to ensure the creation of new data storage objects on FG1 or FG2 without any additional consideration is to set the required filegroup as shown below.

-- Script 2: Create tables in file groups
-- tbl1 would be created on primary file group
CREATE Table tbl1 (ID int identity(1,1))
GO
-- tbl2 would be created on FG1
CREATE Table tbl2 (ID int identity(1,1), fname varchar(20))
ON FG1
GO
-- Verify file group of tbl1
sp_help tbl1
GO
-- Verify file group of tbl2
sp_help tbl2
GO

Now that we created the table on the new filegroup we can try to insert data as follows:

INSERT INTO tbl2 (fname) values ('Atif')
GO

Since we did not specify a file for this filegroup we get this error message:

Another issue we have since we do not have a file associated with this filegroup is if we try to change FG1 to be the default filegroup as shown below:

ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT
GO

We will get this error message, because a file group cannot be set as the default unless it has at least one data file.

 Creating the Data File

A data file may be placed in a single filegroup and cannot be shared among filegroups. Files may be placed in the filegroup at DB creation time or at a later time. In the following script we create secondary data files for each filegroup (FG1 and FG2). (Note: if you do this on your server you will need specify the correct path for the file creation.)

-- Script 3: Add data files to file groups
-- Add data file to FG1
ALTER DATABASE MSSQLTip
ADD FILE (NAME = MSSQLTip_1,FILENAME = 'F:\DBs\2K5\MSSQLTip_1.ndf')
TO FILEGROUP FG1
GO
-- Add data file to FG2
ALTER DATABASE MSSQLTip
ADD FILE (NAME = MSSQLTip_2,FILENAME = 'F:\DBs\2K5\MSSQLTip_2.ndf')
TO FILEGROUP FG2
GO
-- Verify files in file groups
USE MSSQLTip
GO
sp_helpfile
GO
  

Setting New Default Filegroup

After creating the new files for filegroups FG1 and FG2, we can change the default filegroup and verify the new default by running the code below.  

--Script 4: Set FG1 as default file group
-- Set FG1 as default file group
ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT
GO
-- Create a table without specifying file group
Create table table3 (ID TINYINT)
GO
--Verify the file group of table3 is FG1
sp_help table3
GO
-- insert some data to make sure no errors
insert into table3 values (10)
GO

Using sp_help shows that table3 is created in FG1 even without specifying FG1 when creating the table.

Now let's set FG2 as the default filegroup using SSMS. Right click on the MSSQLTip database and click on Properties. Go to the filegroups page and set FG2 as the default as shown below.

Checking the Default Filegroup

In addition to using SSMS to find what filegroup is the default, we can also check the default filegroup by querying sysfilegroups as shown below.

--Script 5: verify default filegroup
USE MSSQLTip
GO
SELECT groupname AS DefaultFileGroup FROM sysfilegroups
WHERE convert(bit, (status & 0x10)) = 1
GO

Summary

Setting the default filegroup does not stop you from using other filegroups it just means that you will need to specify the correct filegroup when creating a table or index otherwise it will be created on whatever filegroup is set as the default.

Next Steps

• Members of db_owner fixed database role can change the default filegroup.
• If used effectively, utilizing multiple filegroups and files you can improve performance by spreading the I/O load. Analyze your current and new databases to take benefit of having multiple filegroups.  Read this tip for more information.
• You can also move storage objects from one filegroup to another.  Read this tip for more information.
• Click here to read more about database file and filegroup architecture

About the author

Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips