Memory Optimized Table is a new concept introduced in SQL Server Hekaton. Memory optimized table is also a table but the major difference between memory-optimized tables and disk based tables is that the memory optimized table information is available in the memory and it does not require the pages to be read into cache from disk. The performance of memory optimized table is much higher than disk based table. Shall discuss its performance in the upcoming article.
Let me create the memory optimized table, but before proceeding with the creating memory table, we should know that whenever you create memory optimized table the database must have memory-optimized data filegroup.
Lets first create a database having memory-optimized data filegroup. Given below is the script.
CREATE DATABASE Sample_DB ON PRIMARY(NAME = [Sample_DB], FILENAME = 'C:\DATA\Sample_data.mdf', size=500MB) ,FILEGROUP [hekaton_demo_fg] CONTAINS MEMORY_OPTIMIZED_DATA( NAME = [hekaton_demo_dir], FILENAME = 'C:\DATA\Sample_dir') LOG ON (name = [hekaton_demo_log] , Filename='C:\DATA\Sample_log.ldf', size=500MB) ; GO
Once you create the database having memory-optimized data filegroup then you can create memory optimized table. Given below is the script.
USE Sample_DB GO CREATE TABLE tbl_sample ( [ID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000), [Name] VARCHAR(50) NOT NULL ) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO
SQL Server Portal 