SQL with Manoj

Hekaton, which also means in-Memory or Memory Optimized Tables, is a Microsoft Code Project for its new version of SQL Server 2014. This version of SQL Server mainly focuses on the high performance In-Memory Database workloads. Which means this version includes Memory Optimized Tables which will reside into Server’s memory and efficiently provides execution of Business Logic by completely reducing I/O between Disk & Memory.

So, as soon as a Memory Optimized Table is created the data it contains will also load into Memory. Let’s see here a Hands-On with Hekaton Tables by simple T-SQL Scripts:

–> Let’s first create a Test Database:

USE [master]
GO

CREATE DATABASE [ManTest]
 CONTAINMENT = NONE
 ON  PRIMARY ( 
	NAME = N'ManTest', 
	FILENAME = N'D:\SQLServer2014\MSSQL\DATA\ManTest.mdf', 
	SIZE = 5120KB, 
	FILEGROWTH = 1024KB 
 )
 LOG ON ( 
	NAME = N'ManTest_log', 
	FILENAME = N'D:\SQLServer2014\MSSQL\Log\ManTest_log.ldf', 
	SIZE = 1024KB, 
	FILEGROWTH = 10%
 )
GO

–> Now we will create a FileGroup and associate the [ManTest] Database with it:

USE [ManTest]
GO

-- Add MEMORY_OPTIMIZED_DATA filegroup to the database.
ALTER DATABASE [ManTest] 
	ADD FILEGROUP [ManTestFG] CONTAINS MEMORY_OPTIMIZED_DATA 
GO

-- Add file to the MEMORY_OPTIMIZED_DATA filegroup.
ALTER DATABASE [ManTest]
ADD FILE
  ( NAME = ManTestFG_file1,
    FILENAME = N'D:\SQLServer2014\MSSQL\DATA\Datasample_database_1') -- You might need to check and correct the path here.
TO FILEGROUP ManTestFG
GO

A SQL Server FileGroup is required to store memory_optimized_data in a SQL Server Database. As per MSDN this FileGroup will be used to guarantee durability of memory-resident data in the event of a server crash or restart. During the crash recovery phase in server startup, the data is recovered from this FileGroup and loaded back into memory.

–> Now we will create Memory Optimized Table with new Options added in CREATE TABLE syntax:

-- Create memory optimized table and hash indexes on the memory optimized table:
CREATE TABLE dbo.Person_memoryOptimizedTable
(
	PersonID INT NOT NULL
		PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 19972),
	
	PersonName NVARCHAR(100) NOT NULL, 
	
	DateAdded DATETIME NOT NULL

) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)
GO

The Options:
– NONCLUSTERED HASH WITH : Clustered Index not allowed (as indexes are not stored, but re-created after SQL Server is restarted), so a PK should be defined with NonClustered Index of Type ‘Hash’.
– BUCKET_COUNT : Indicates the number of buckets that should be created in the Hash index.
– MEMORY_OPTIMIZED=ON : makes the table Memory Optimized.
– DURABILITY = SCHEMA_AND_DATA : makes the Table and its Data available all the time in-Memory.

Note:
– Indexes can only be created online with CREATE TABLE statement.
– If you don’t provide the DURABILITY = SCHEMA_AND_DATA option, then the Data will be persisted in the Table only.
– The Option DURABILITY = SCHEMA_ONLY will not make the Table Data Durable on Disk and data will lost after Server Restart or Crash, but the Table Schema will be available.

–> Let’s insert some records in the table created above:

-- Inserting records into the Memory Optimized Table from [Person] table in [AdventureWorks] DB:
INSERT INTO dbo.Person_memoryOptimizedTable
SELECT 
	[BusinessEntityID], 
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS PersonName, 
	GETDATE() AS DateAdded
FROM [AdventureWorks2012].[Person].[Person]

You will get following error:
Msg 41317, Level 16, State 3, Line 34
A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user database or databases model and msdb, and it cannot write to master.

This means that you cannot insert records into Memory Optimized Tables across databases.
So, we have a workaround here, we will insert records into a #temp table first and then insert from this #temp table.

-- So, I'll insert records from [AdvantureWorks] Database to #temp table first:
SELECT 
	[BusinessEntityID], 
	CONCAT([FirstName], ' ', [MiddleName], ' ', [LastName]) AS PersonName, 
	GETDATE() AS DateAdded
into #tempPerson
FROM [AdventureWorks2012].[Person].[Person]
-- (19972 row(s) affected)

-- Now, I can insert records from #temp table into the Memory Optimized Table:
INSERT INTO dbo.Person_memoryOptimizedTable
SELECT 
	[BusinessEntityID], 
	PersonName, 
	DateAdded
FROM #tempPerson
-- (19972 row(s) affected)

-- Let's check contents of the Table:
SELECT * 
FROM Person_memoryOptimizedTable
GO

So, we here saw how to create Memory Optimized Tables and create a Separate Database to store them by allocating a FileGroup to the DB.

-- Final Cleanup
DROP TABLE dbo.Person_memoryOptimizedTable

DROP TABLE #tempPerson
GO

USE master
GO

DROP DATABASE ManTest
GO

Update: Know more about In-Memory tables:

Next post I’ll discuss more about Memory Optimized tables, their benefits and limitations.