Add a second filegroup to an existing SQL server database

GOAL

Standard SQL server databases are setup to use 2 files one for the data and one for the log. The data file is then part of the PRIMARY filegroup.

In my case there is a database with 3 types of tables:

• lookup or dimension tables that hardly ever change
• history tables that keep growing daily
• work tables where a lot of inserting and deleting of records takes place.

To maintain the database the work tables need to be reorganized, the database files need to be shrunk. To avoid doing all the work of reorganizing and shrinking on one huge database file, I have decided to add a 2nd filegroup that will contain the work tables. This filegroup can then be reorganized more frequently than the one that contains other 2 types of tables. As and added advantage I have to option to store the filegroups on different disks if performance or storage space becomes an issue.

This article explains how to create a new filegroup before the tables are created. It does not explain how to migrate existing tables from one filegroup to another one.

Create a new Filegroup interactively with SqL Server Management Studio

• Connect the object explorer to the SQL server.
• In the object explorer right-click on the Database name, select Properties.
• In the database properties window, under Select a page, select Filegroups.
• Click on the Add button
• In the Rows grid add the name of the filegroup, e.g. FILEGROUPWORK, leave Read-Only and Default unchecked
• under Select a page, select Files
• Click on the Add button
• In the Database files grid
• Logical Name: give a name that starts with the database name and ends with the same suffix as the fielgroup. e.g. MyDatabaseWORK.
• File type: Rows data
• Filegroup: select the new filegroup from the dropdown list, e.g. FILEGROUPWORK
• Initial size: whatever you expect to be a good starting size
• Autogrowth: whatever is needed to accommodate adding new data efficiently
• Path: if nececessary the path can be different from the primary group.
• Filename: use the same name as the Logical name and add the .NDF file extension.
• Click OK to create the filegroup and close the database properties window.

Create a table in the filegroup INTERACTIVELY 

A default create table statement will create the table in the [PRIMARY] filegroup. When designeing a new table interactively, the designer window has a properties window in which you can select another filegroup from a dropdown.

alternatively if you create the table with a script, then the [PRIMARY] text must be replaced by the alternative filegroup name, e.g. [FILEGROUPWORK]

CREATE TABLE [Sales].[Currency](

    [CurrencyCode] [nchar](3) NOT NULL,

    [Name] [dbo].[Name] NOT NULL,

    [ModifiedDate] [datetime] NOT NULL,

 CONSTRAINT [PK_Currency_CurrencyCode] PRIMARY KEY CLUSTERED 

(

    [CurrencyCode] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) 

    ON [FILEGROUPWORK] 

) ON [FILEGROUPWORK]