‘In-Memory OLTP’ also known as ‘Hekaton’ and ‘In-Memory Optimization’ is in-memory processing technology that allows you to get most out of SQL Server for your transactional workloads. This feature was introduced with SQL Server 2014 release. This post demonstrates the usage of In-Memory OLTP feature with examples.
Create A Database
-- Create a Database CREATE DATABASE TestInMemoryOLTPDB
Create The Memory Optimized Filegroup
To create memory-optimized tables, you must first create a memory-optimized filegroup to hold data for our database. In case of server crash, unexpected restarts this filegroup will ensure durability and data recovered from this filegroup to memory.
You can only create one memory-optimized filegroup per database. You need to explicitly mark the filegroup as containing MEMORY_OPTIMIZED_DATA.
ALTER DATABASE TestInMemoryOLTPDB ADD Filegroup TestInMemoryOLTPDB_FileGroup CONTAINS MEMORY_OPTIMIZED_DATA
The key word “MEMORY_OPITIMIZED_DATA” tells SQL Server, this file group is in memory and will store memory-optimized objects.
It can raise an SQL error saying, ‘The operation ‘AUTO_CLOSE’ is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.’
To resolve that you need to set ‘AUTO_CLOSE’ OFF. Read here.
You need to add one or more data container to the MEMORY_OPTIMIZED_DATA filegroup.
1 2 3 4
ALTER DATABASE TestInMemoryOLTPDB ADD FILE (Name = 'TestInMemoryOLTPDB_P', FileName = 'C:\Dev\SQL Server\TestInMemoryOLTPDB_P') TO FileGroup TestInMemoryOLTPDB_FileGroup
Once you create a memory-optimized filegroup, you can’t remove it. To do that, you need to drop the database.
Create Memory-Optimized Tables
Now, we can create memory-optimized tables to hold our data. See the below code:
1 2 3 4 5 6 7 8 9 10 11 12 13
USE [TestInMemoryOLTPDB] -- create a durable (data will be persisted) memory-optimized table CREATE TABLE dbo.Employee ( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 10000), Name VARCHAR(200) NULL, Address VARCHAR(200) NULL, CreatedDate DATETIME NULL ) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA) GO
By mentioning “MEMORY_OPTIMIZED = ON” in WITH clause, we are telling SQL Server that this is a memory-optimized table.
Unlike disk-based tables, memory-optimized tables support two kind of indexes :
- Non-Clustered hash indexes
- Non-Clustered range indexes
Memory-optimized tables can be durable(data persistence) or non-durable in nature and that is specified using DURABILITY value.
To create a durable memory-optimized table, use DURABILITY = SCHEMA_AND_DATA and
for non-durable, use DURABILITY = SCHEMA_ONLY
Loading Data Into The Memory-Optimized Tables
Let’s add some dummy records. See the below codes:
1 2 3 4 5 6 7 8
DECLARE @i INT = 1 WHILE @i <= 10000 BEGIN INSERT INTO dbo.Employee (ID,Name,Address,CreatedDate) VALUES(@i, 'EMP'+@i, NULL,GETDATE()) SET @i = @i+1 END
Validate the Table Data
Try to retrieve the table data. If it returns added data, means its working.
SELECT * FROM Employee WHERE ID = 2000
So, it was all about In-Memory OLTP feature, if you have any query then please comment below and let us know. If you liked this article, then please follow us on Facebook to get notification for new posts.
Rahul is a Data Geek, technology enthusiast, a passionate writer, thinker with passion for computer programming. He loves to explore technology and finds ultimate joy when writing about trending technology, geek stuff and web development.