This article explains how to manage a memory-optimized filegroup of SQL Database. The memory-optimized filegroup contains the memory-optimized tables and table variables. I have written two articles that explain how we can migrate the disk-based tables to memory-optimized tables. Now, we will learn how to add memory-optimized filegroups in a SQL database. I will cover the following topics in the article.

1. Add a memory-optimized filegroup in SQL Database using SQL Server management studio and T-SQL queries
2. Create a memory-optimized table in a specific file group

I am using SQL Server 2019 and stackoverflow2010 database. The Stackoverflow database contains tables. First, let us see how we can add a memory-optimized filegroup.

Add memory-optimized filegroup using SSMS

To add a memory-optimized filegroup, Open SSMS and connect to SQL Server database engine 🡪 Expand databases🡪 Right-click on Stackoverflow2010 and select Properties.

A dialog box named Database Properties opens. In the dialog box, select the Filegroups option. In the MEMORY OPTIMIZED FILEGROUP section, click on Add Filegroup.

A row in the filegroup grid-view will be created. Specify the desired name of the memory-optimized filegroup in the Name column of the grid view.

Click OK to create a filegroup. Now, we must add a data file to the FG_MemoryOptimized_Users filegroup. To do that, click on Files in the Database Properties dialog box and click on Add.

A row in the Database files grid-view, a row will be added. Specify the following details:

1. Logical Name: Specify desired logical name of the memory-optimized filegroup
2. File type: Select FILESTREAM Data in the File type column
3. Filegroup: The file group name will be retrieved automatically
4. Path: In the Path column, specify the location in which you want to store the memory-optimized tables

Following is the screenshot of the files pan of Database Properties.

Click OK to add a data file. Now, run the following query to populate the details of the memory-optimized filegroup.

Query Output

As you can see in the above image, the filegroup is created. You can view the data files of the memory-optimized filegroup in D:\FG_MemoryOptimized_Users\FG_MemoryOptimized_Users location.

Now, let us create a memory-optimized filegroup in the SQL database using a T-SQL query.

Add memory-optimized filegroups using T-SQL queries

We can use ALTER DATABASE ADD FILEGROUP CONTAINS statement to add a filegroup in the existing SQL Database. The syntax to add a memory-optimized filegroup is as follows:

In the syntax:

1. database_name: Specify the database name in which you want to add the memory-optimized filegroup
2. file_group_name: Specify the desired filegroup name that you want to add to the SQL Database
3. CONTAINS: You can specify the type of data you want to store in the filegroup. We want to add memory-optimized data, so specify MEMORY_OPTIMIZED_DATA

In our case, we are adding FG_MemoryOptimized_Posts in a Stackoverflow2010 database. To do that, run the following query statement.

Once the filegroup is added, let us add a datafile to the FG_MemoryOptimized_Posts filegroup. Add data file in the memory-optimized file-group. To add a datafile, we can use ALTER DATABASE ADD DATAFILE TO FILEGROUP statement. The syntax to add a data file is as the following:

In the syntax,

1. database_name: Specify the database name in which you want to add the memory-optimized filegroup
2. file_group_name: Specify the desired filegroup name that you want to add to the Database
3. ADD FILE: Specify the details of the data file that you want to add to a filegroup. In the ADD FILE keywords, we must specify the following parameters
   1. Logical_file_name: Specify the desired name of the filegroup
   2. Memory_Optimized_Filegroup_Location: Specify the location of the directory in which you want to store the memory-optimized data
4. FILE_GROUP_NAME: Specify the name of the filegroup in which you want to add the data file. The filegroup name must be specified after the TO FILEGROUP keyword

In our case, we want to add a data file named DF_MemoryOptimized_Posts in the FG_MemoryOptimized_Posts filegroup. The add a data file, run the following command.

Once the data file is added, run the below query to view the list of filegroups.

Query Output

As you can see, the data file named DF_MemoryOptimized_Posts has been added in the FG_MemoryOptimized_Posts filegroup.

Create a table in the memory-optimized filegroup

Now, let us create a simple table named want to create a table named tblArchivedPosts in FG_MemoryOptimized_Posts filegroup, the query to create a table will be the following.

Once query executed successfully, let us verify that table is created in FG_MemoryOptimized_Posts file group. To do that, run the following query

Query Output

As you can see, the tblArchivedPosts table is in the FG_MemoryOptimized_Posts filegroup.

Summary

In this article, we learned how we could create a memory-optimized filegroup in an existing database. We learned the following topics.

1. Add multiple memory-optimized filegroups in SQL Database using SQL Server management studio and T-SQL queries.
2. Create a memory-optimized table in a specific file group.

• Author
• Recent Posts

Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration.

He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

Latest posts by Nisarg Upadhyay (see all)

• Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
• Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
• Changing the location of FILESTREAM data files in SQL Database - October 14, 2022