Filegroups - Creating a new file group and making it the default

Ref-1
https://blog.sqlauthority.com/2009/05/31/sql-server-create-multiple-filegroup-for-single-database/

https://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/07/11/881.aspx

https://www.sqlshack.com/database-table-partitioning-sql-server/

https://blog.sqlauthority.com/2013/08/15/sql-server-sql-basics-what-are-filegroups-day-9-of-10/

Filegroups - Creating a new file group and making it the default

There are many occaisons where you may want to use multiple filegroups, especially if dealing with large tables and fragmentation is a worry.

The script below shows a complete end-to-end example of how to create a file group and mark it as the default so all newly created objects are placed on there.

Note, there is nothing you can do about [PRIMARY] so if you have creation scripts or drop / recreate index scripts with [PRIMARY] in them then that data will always go on the [PRIMARY] file group.

-- Create the databaseCREATE DATABASE test_filegroupgo
USE test_filegroupgo
-- Create a new file groupALTER DATABASE test_filegroup
   ADD FILEGROUP NonClustIndexesgo
-- Add a file to the file group, we can now use the file group to store data
ALTER DATABASE test_filegroup   ADD FILE (      NAME = NonClustIndexes,      FILENAME = 'E:\MSSQL\DEV\NonClustIndexes.ndf',      SIZE = 5MB,
      MAXSIZE = 100MB,      FILEGROWTH = 5MB      )   TO FILEGROUP NonClustIndexes
go
-- Change the default filegroup for where new tables and indexes are created.ALTER DATABASE test_filegroup   MODIFY FILEGROUP NonClustIndexes DEFAULT;
GO
-- Test thisCREATE TABLE xyz (   mydata int not null constraint pk_xyz primary key clustered,
   ) ON [PRIMARY]
INSERT xyz values( 1234 )GO
-- This shows that the table was created on the [PRIMARY] file group and not our new user defined groupsp_help xyzgo
-- This does not move the tableDBCC DBREINDEX( xyz )go
-- Nor does thisCREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTINGGO
-- This does thoughCREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTING ON NonClustIndexesGO
-- Table is now located on the NonClustIndexes filegroupsp_help xyzGO
-- Still on the NonClustIndexes filegroupDBCC DBREINDEX( xyz )GO
-- And still there.CREATE UNIQUE CLUSTERED INDEX pk_xyz ON xyz( mydata ) WITH DROP_EXISTINGGO
-- What about new objects?CREATE TABLE z (   mydata int not null constraint pk_z primary key clustered,   )GO
-- Correctly placed on our NonClustIndexes filegroupsp_help z

Filed under: SQL Server







========================================================================

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')