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:
- Create one mdf (main datafile) in the primary filegroup.
- Create two ndfs (secondary datafiles) in a user-defined filegroup called Order_Hist located on a separate drive.
- 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
(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')
(NAME = RaticCo_Log, FILENAME = 'E:\SQL\RatisCoLog.ldf')
Get the book for yourself and your friend. This is just a reference everyone must have it.
Reference: Pinal Dave (https://blog.sqlauthority.com)