In Memory OLTP (Hekaton) introduced in SQL Server 2014 is a new database engine component which is optimized for OLTP workloads to achieve the performance by storing data in memory and accessing data from memory. Memory optimized tables can be accessed using Transact –SQL. When memory optimized tables are accessed all data is stored into the memory.
Today, I will tell you how to create a memory optimized tables.
Let’s first create a database to stored memory optimized tables. Database that contain memory optimized tables must have MEMORY_OPTIMIZED_DATA filegroup. Syntax to create filegroup is same as creating regular filegroup only difference is that here we need to specify option CONTAINS MEMORY_OPTIMIZED_DATA.
–Create a database
IF EXISTS (SELECT * FROM sys.databases WHERE name=’InMemOLTP’)
DROP DATABASE InMemOLTP;
CREATE DATABASE InMemOLTP
PRIMARY(NAME = [InMemOLTP_data],
FILENAME = ‘I:\453491\InMemOLTP\Data\HKDB_data.mdf’, size=500MB),
FILEGROUP [InMemOLTP_fg] CONTAINS MEMORY_OPTIMIZED_DATA
(NAME = [InMemOLTP_mod_dir],
FILENAME = ‘I:\453491\InMemOLTP\InMemOLTP_mod_dir’)
LOG ON (name = [InMemOLTP_log],
Here, I have created a filegroup ‘InMemOLTP_fg’ with filecontainer ‘InMemOLTP_mod_dir’. I have also specifies BIN2 collation as currently indexes on character column can only be defined on columns that uses BIN2 collation.
Below is the script to create a memory optimized table with name ‘Test_OLTP’.
CREATE TABLE Test_OLTP
Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),
) WITH (Memory_Optimized = ON, DURABILITY = SCHEMA_AND_DATA);
Syntax to create memory optimized table is similar to the Transact SQL tables but to specify a memory optimized table we need to define clause MEMORY_OPTIMIZED = ON. Memory optimized table can be defined using two durability – SCHEMA_AND_DATA and SCHEMA_ONLY.
With SCHEMA_ONLY durability, memory optimized table will store only table structure and SQL server will not store log changes to the disk. With SCHEMA_AND_DATA, memory optimized table store table data to the checkpoint files on disk and table structure too.
Nonclustered Hash index is created on Name column as clustered indexes are not allowed on memory optimized tables. If you don’t specify Nonclustered with Primary Key column you will get error –
Msg 12317, Level 16, State 78, Line 21
Clustered indexes, which are the default for primary keys, are not supported with hash indexes. Specify a NONCLUSTERED index instead.
When we create a HASH index we need to specify the number of buckets in the hash index. More on Bucket_count I will discuss later in further posts.
Now, the table has been created, let’s try to insert some data into memory optimized table.
INSERT INTO Test_OLTP (Name, Place, CompanyName, Skill)
VALUES (‘Kapil’, ‘Gurgaon’,’CTS’,’SQL Server’),
Just check the data that you has inserted by running below query.
SELECT * FROM Test_OLTP
So friends, in this way we can create memory optimized database and table. We will discuss further on memory optimized tables in my next blogs.
Have a happy learning 🙂