SQL SERVER – SQL Basics: What Are Filegroups – Day 9 of 10

This is the 9th post out of my 10 post series of my 10th book – SQL Basics. Today will show the importance of data and information.

You can get that in Paperback (USA) and Kindle (Worldwide).

Using Filegroups

There are many advantages to using filegroups to manage the database workload. A filegroup may contain many datafiles, and the properties of all the datafiles can be managed simultaneously with a filegroup.

Primary and Secondary Filegroups

A primary filegroup contains the primary datafile (mdf) and possibly secondary datafiles (ndf). All system tables are allocated to the primary filegroup.

A secondary filegroup (also called a user-defined filegroup) contains secondary datafiles (ndf) and database objects.

The default filegroup contains objects which were created without an assigned filegroup. The primary filegroup is the default filegroup unless another filegroup is specified.

Logfiles are never part of a filegroup. We learned about logfiles in yesterdays post. The logfile tracks all the changes that have taken place since the last database backup, whereas datafiles have the file extension .mdf or .ndf, logfiles always have the .ldf extension.

In the figure below we have one datafile called RatisCo_Data.mdf in the SQL folder of the C drive (C:\SQL\RatisCo_Data.mdf). Since we didn’t specify any filegroups, SQL Server automatically placed it in the primary filegroup for us. We also created a logfile in the same location (C:\SQL\RatisCo_Log.ldf). That file was not placed inside a filegroup, since logfiles are never part of a filegroup.

Our next example will create one datafile in the primary filegroup and two datafiles in the secondary filegroup (also known as the user-defined filegroup).

We can accomplish this with the following steps:

1. Create one mdf (main datafile) in the primary filegroup.
2. Create two ndfs (secondary datafiles) in a user-defined filegroup called Order_Hist located on a separate drive.
3. Create the ldf (log datafile) on a separate drive.

Each data file has its properties set in its own set of parenthesis.  If you have two parentheses after a filegroup name then that filegroup will have two datafiles (like the [OrderHist] filegroup in the code below). The framework for the code is seen here.

If our goal is to put the mdf on the C: the two ndf files on the D: and the log on the E: then our code would be completed by doing the following:

CREATE DATABASE RatisCo
ON PRIMARY
(NAME = RaticCo_Data, FILENAME = 'C:\SQL\RatisCo_Data1.mdf'),
FILEGROUP [OrderHist] (NAME = RaticCo_Hist1, FILENAME = 'D:\SQL\RatisCo_Hist1.ndf'),
(NAME = RaticCo_Hist2, FILENAME = 'D:\SQL\RatisCo_Hist2.ndf')
LOG ON
(NAME = RaticCo_Log, FILENAME = 'E:\SQL\RatisCoLog.ldf')
GO


Action Item

Get the book for yourself and your friend. This is just a reference everyone must have it.

Available in Paperback (USA), Kindle (Worldwide) 

Reference: Pinal Dave (https://blog.sqlauthority.com)