This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, I am showing an Optimized Memory Table with allocating dedicated Memory Optimized File Group in SQL Server 2014.
In the Previous post, I discussed basic theory about In-Memory Engine of SQL Server 2014.
That is one of the optimized ways to the create an Optimized Memory Table in a dedicated File Group of SQL Server.
TSQL Script for creating the Memory Optimized File group and file:
1 2 3 4 5 6 7 8 9 |
USE [master] GO ALTER DATABASE [AdventureWorks_2014] ADD FILEGROUP [Memory_Optimized] CONTAINS MEMORY_OPTIMIZED_DATA GO ALTER DATABASE [AdventureWorks_2014] ADD FILE ( NAME = N'Hekaton', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Hekaton' ) TO FILEGROUP [Memory_Optimized] GO |
Create a sample Memory Optimized Table:
1 2 3 4 5 6 7 8 9 |
USE AdventureWorks_2014 GO CREATE TABLE dbo.tbl_Students ( StudentID int NOT NULL ,Name VARCHAR(50) ,Address VARCHAR ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO |
Above table will create automatically into a Memory Optimized file group. We can access this table like any other table.