Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


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.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

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

Categories


© Copyright 2020 SQLTraining Ltd.