In Memory Table in SQL Server

ByHariharan Rajendran

In Memory Table in SQL Server

In a normal database, we can not create a in-memory tables. To create a in-memory table, we need to follow the below rules.

1. Create or Alter database to add a memory optimized data file group

2. Add a file into the file group.

Steps to alter your database to support in-memory tables. Before that let us try to create in-memory table in normal database.

image

Step 1: Alter the database to add memory optimized file group.

ALTER DATABASE [SampleDB] ADD FILEGROUP Analytics_Mode CONTAINS MEMORY_OPTIMIZED_DATA

 

Step 2: Add a file into the file group. Try to provide the path where SQL Server is installed.

ALTER DATABASE [SampleDB] ADD FILE (name=‘Analytics_Mode’,filename=‘E:\Local_Install_Applications\SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\InMemory1’) TO FILEGROUP Analytics_Mode

 

In the above script “InMemeory1” is a directory, don’t create this directory manually. The above script will create automatically and will populate the files.

Step 3: Now run the in-memory table script.

CREATE TABLE Account (

    ID int NOT NULL PRIMARY KEY NONCLUSTERED,

    AccountDescription nvarchar (50),

    AccountType nvarchar(50),

    Unit int

    )

    WITH (MEMORY_OPTIMIZED = ON );

GO

image

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a MVP in Data Platform and Microsoft Certified Trainer with 10+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!