What Are Filegroups
Published: May 14, 2017
Well, let’s be honest here… in a nutshell a Filegroup is… wait for it… a group of files.

Bet you didn’t see that one coming.

However, it’s their uses which are actually worth discussion because they provide some useful functionality as a whole.

So, you can create multiple data files (ndf files) alongside your main mdf and use them to house your database on multiple drives or different folders, but how is this any use if you don’t know which objects are actually in which file?

The way to do this is filegroups.

Let’s create a fake example in which we have our data file on a C drive (you shouldn’t do this, but as an example we’re going to go with it), but we now have an SSD inserted into our server as a D drive and we want to create a new, much larger, traffic intensive table into our database which we want to run solely on the SSD away from the rest of the database…

I’m not a big fan of SSMS so I’m going to use tSQL in order to add a new filegroup to the database then add a data file on another drive, allocate the data file to the filegroup, and then add our new table into that specific data file. Simple enough, yes?

So let’s create our test database:

use master
go

if exists
(
  
select *
  
from sys.databases
  
where name = 'testFileGroup'
)
begin
   alter database
testFileGroup set single_user with rollback immediate

   drop database
testFileGroup
end
go

create database testFileGroup on primary
(
  
name = 'testFileGroup',
  
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testFileGroup.mdf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
)
log on
(
  
name = 'testFileGroup_Log',
  
filename = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\testFileGroup_log.ldf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
)
go


Now we’ll create a table and put some random data in it:

use testFileGroup
go

create table testTable
(
  
id int identity(1, 1),
  
value char(100) default('a')
)
go

insert into testTable default values
go 10


Now we’ll add a filegroup to our database, then add a file to our database, in that new filegroup, and on the new SSD:

alter database testFileGroup
add filegroup newSSDFilegroup
go

alter database testFileGroup
add file
(
  
name = 'testFileGroupSSD',
  
filename = 'D:\SQLData\testFileGroupSSD.ndf',
  
size = 10MB, maxsize = unlimited, filegrowth = 10MB
) to filegroup newSSDFileGroup
go


Finally we can add a new table into that filegroup, guaranteeing that it is then stored on our new, faster disk:

create table testTable2
(
  
id int identity(1, 1),
  
value char(100) default('a')
)
on newSSDFileGroup
go


And that’s it… nice and simple introduction to filegroups and one way in which they can be very useful.
Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron