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.