As a SQL Server enthusiast, I receive numerous questions from my readers every day. Today, I want to address a question that I have received multiple times in the past year but haven’t had the chance to write about until now. The question is: How can we create multiple filegroups for a database in SQL Server?

To answer this query, let’s explore four different scenarios:

1) Creating a New Database

If you want to create a new database with multiple filegroups, you have two options: using T-SQL or using SQL Server Management Studio (SSMS).

a) Using T-SQL

Here’s an example of how you can create a new database with multiple filegroups using T-SQL:

CREATE DATABASE [FGTest]
ON PRIMARY (
    NAME = N'FGTest',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest.mdf',
    SIZE = 3072KB,
    FILEGROWTH = 1024KB
),
FILEGROUP [Secondary] (
    NAME = N'FGTest_2',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_2.ndf',
    SIZE = 3072KB,
    FILEGROWTH = 1024KB
)
LOG ON (
    NAME = N'FGTest_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_log.ldf',
    SIZE = 1024KB,
    FILEGROWTH = 10%
)
GO

b) Using SSMS

If you prefer a graphical interface, you can use SSMS to create a new database with multiple filegroups. Simply follow these steps:

1. Right-click on the database and select “Properties”.
2. Navigate to the “Filegroup” section.
3. Add a new filegroup by providing a name.

2) Updating an Existing Database and Adding a Filegroup

If you already have an existing database and want to add a new filegroup to it, the process is relatively straightforward. Let’s take a look at how you can accomplish this using T-SQL and SSMS.

a) Using T-SQL

To add a new filegroup to an existing database using T-SQL, you can execute the following command:

USE [master]
GO
ALTER DATABASE [FGTest] ADD FILEGROUP [Third]
GO

b) Using SSMS

If you prefer a visual approach, you can add a new filegroup to an existing database using SSMS by following these steps:

1. Right-click on the database and select “Properties”.
2. Navigate to the “Filegroup” section.
3. Add a new filegroup by providing a name.

I encourage all my readers to continue sending me your questions and doubts. I will do my best to address them in future blog posts. Stay tuned for more SQL Server tips and tricks!