- In Memory Optimized Tables SQL 2014
What are Memory Optimized Tables in SQL Server?
Memory Optimized tables are newly introduced in SQL Server and they are fully ACID compliant data storage structures, which stores the data mainly within the system memory(RAM), allowing users to access data very very quickly compared to the stored File system stored Table.
The transactional consistency is maintained in Memory Optimized Tables in SQL Server 2014 by:
In-Memory Row Versioning:
Memory Optimized tables maintains various versions of the same row dispersed over the in-memory data structure of the table which allows the reading and writing of the rows concurrently without transactional inconsistency issues. It is equivalent to SNAPSHOT ISOLATION LEVEL where row-versioning handles conflicts by throwing an error to the application which expects the application to implement some sort of retry attempts logic for failed transactions.
Data durability:
Transaction Log: Logging to the Transaction Log is a disk-based operation, and so this might become a bottleneck in a highly transactional In-Memory environment.
Checkpoint and Delta files:
These Files are placed on a special FileGroup for Memory-Optimized storage which are written to sequentially and are on SQL Server start-up to load in the Memory-Optimized tables in a database to Memory.
To create Memory Optimized tables:
Step 1:
USE [master]
GO
ALTER DATABASE [In-Mem] ADD FILEGROUP [mem] CONTAINS MEMORY_OPTIMIZED_DATA
GO
ALTER DATABASE [In-Mem] ADD FILE ( NAME = N'In-Mem_mod1', FILENAME = N'C:\In-Mem\In-Mem_mod1' ) TO FILEGROUP [mem]
GO
Step 2
USE [In-Mem]
GO
CREATE TABLE dbo.In-MemTable (
Id int not null primary key nonclustered hash with (bucket_count=10000),
AltId int not null index ix_naturalId nonclustered hash with (bucket_count=5000),
Name nvarchar(100) null
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_AND_DATA)
GO
I will add more options for these tables shortly.