Creating and Accessing In-Memory OLTP Databases and Tables

This is the second article in a series of articles about SQL Server In-Memory OLTP.

The introductory article — SQL Server In-Memory OLTP, briefly introduced the basics of the new Hekaton engine. In this part, we will focus on practice. To be more specific, we will see how to create In-Memory optimized databases and tables, and also, how to assess them with help of T-SQL.

Prerequisites to start using memory-optimized databases

In-memory OLTP is automatically installed with a 64-bit Enterprise or Developer edition of SQL Server 2014 or SQL Server 2016. SQL Server 32-bit edition does not provide In-Memory OLTP components.

Thus, if you have 64-bit Developer edition of SQL Server installed on your computer, you may start creating databases and data structures that will store memory-optimized data with no additions setup.

Every single database that is going to contain memory-optimized tables should contain one MEMORY_OPTIMIZED_DATA filegroup. This filegroup contains one or several containers. Every single container stores data and/or delta files. SQL Server uses these files to recover the memory-optimized tables. Containers may be placed on the different disk arrays,
similarly to FILESTREAM filegroups.

The syntax for creating memory-optimized filegroup is almost the same as for a traditional FILESTREAM filegroup, with the several differences:

1. Only one memory-optimized filegroup can be created for a database.
2. The CONTAINS MEMORY_OPTIMIZED_DATA option must be specified explicitly.

You can create the filegroup in the process of creating a database:

CREATE DATABASE InMemoryDemo
ON PRIMARY
(
NAME = N'InMemoryDemo',
FILENAME = N'D:\Data\InMemoryOLTPDemo.mdf'
),
FILEGROUP IMOFG CONTAINS MEMORY_OPTIMIZED_DATA
(
NAME = N'InMemoryDemo_Data',
FILENAME = N'D:\IMOFG\InMemoryDemo_Data.mdf'
)

Alternatively, you can add the MEMORY_OPTIMIZED_DATA filegroup to an existing database, and then add files to that filegroup.

-- Adding the containers
ALTER DATABASE
  DemoDB ADD FILE
  (
  NAME = 'DemoDB_Mod',
  FILENAME = 'D:\Data\DemoDB_Mod'
  )
  TO FILEGROUP DemoDB_Mod

Internally, In-Memory OLTP uses a streaming mechanism based on the FILESTREAM technology, that is well adapted for sequential I/O access.

Creating memory-optimized tables

Now, we have everything we need to start creating Memory-Optimized objects. Let’s create a Memory-Optimized table.

The syntax for creating in-memory-optimized tables is very similar to one for creating disk-based tables. However, there are a few extensions and restrictions:

1. The MEMORY_OPTIMIZED = ON clause identifies a table as in-memory optimized.
2. In-memory-optimized tables do not support all the data types that traditional tables support. The following data types are not supported:

• datetimeoffset
• geography
• geometry
• hierarchyid
• rowversion
• XML
• sql_variant
• User-Defined Types

A memory-optimized table can be created with the following durability values: SCHEMA_AND_DATA or SCHEMA_ONLY. SCHEMA_AND_DATA is the default value.
In case you specify SCHEMA_ONLY, all changes to the table will not be logged and the table data is not stored on the disk.

Every single memory-optimized table must contain at least one index. Note that the PRIMARY KEY constraint implicitly creates an index. A durable memory-optimized table always requires a PRIMARY KEY constraint.

CREATE TABLE dbo.Person (
  [Name] VARCHAR(32) NOT NULL PRIMARY KEY NONCLUSTERED
 ,[City] VARCHAR(32) NULL
 ,[Country] VARCHAR(32) NULL
 ,[State_Province] VARCHAR(32) NULL
 ,[LastModified] DATETIME NOT NULL
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

Сomposite indexes may be added when all columns have been created:

CREATE TABLE dbo.Person (
  [Name] VARCHAR(32) NOT NULL PRIMARY KEY NONCLUSTERED
 ,[City] VARCHAR(32) NULL
 ,[Country] VARCHAR(32) NULL
 ,[State_Province] VARCHAR(32) NULL
 ,[LastModified] DATETIME NOT NULL
 ,INDEX T1_INDX_C1C2 NONCLUSTERED ([Name], [City])
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);

When you create the in-memory optimized table, the In-Memory OLTP engine creates DML routines to be able to access that table. It loads routines as DLL files. For a specific operation, SQL Server calls a required DLL file.

Altering tables and indexes

It was impossible to ALTER tables before SQL Server 2016. To make schema changes, you had to drop and recreate the in-memory table.

In the new release of SQL Server, ALTER TABLE is partially supported.

SQL Server 2016 provides you the ability to perform off-line operations: adding and dropping (modifying) columns, indexes, and constraints. Also, it is now possible to work with in-memory tables using the SSMS table designer or dbForge Studio for SQL Server table editor.

Note that ALTER TABLE requires rebuilding of the table. That is why you need to be sure you have enough memory before running this operation. During the rebuild operation, every single row is reinserted into the new table and the table is not available while the ALTER operation is being performed.

You can introduce multiple changes to a single table and combine them into a single ALTER TABLE statement. You can ADD columns, indexes, and constraints and you can DROP columns, indexes, and constraints. Note that you can not combine ADD and DROP commands together in a single ALTER TABLE.

-- index operations
-- change hash index bucket count
ALTER TABLE dbo.TableName ALTER INDEX IX_Name REBUILD WITH (BUCKET_COUNT = 131072);
GO
-- add index
ALTER TABLE dbo.TableName ADD INDEX IX_Name NONCLUSTERED (ColName);
GO
-- drop index
ALTER TABLE dbo.TableName DROP INDEX IX_Name;
GO
-- add multiple indexes
ALTER TABLE dbo.TableName ADD INDEX IX_Name NONCLUSTERED (ColName),
 INDEX IX_Name2 NONCLUSTERED (ColName2);
GO
-- Add a new column and an index 
ALTER TABLE dbo.TableName ADD Date DATETIME, INDEX IX_Name NONCLUSTERED (ColName);
GO
-- Drop a column
ALTER TABLE dbo.TableName DROP COLUMN ColName;
GO

Table types and table variables

SQL Server 2016 provides you the ability to create memory-optimized table types that you can use while defining a table variable:

CREATE TYPE TypeName
AS TABLE (
Col1 SMALLINT NOT NULL,
Col2 INT NOT NULL,
Col3 INT NOT NULL,
Col4 INT NOT NULL,
INDEX IX_Col1 NONCLUSTERED HASH (Col1)
WITH (BUCKET_COUNT = 131072),
INDEX IX_Col1 NONCLUSTERED (Col2))
WITH (MEMORY_OPTIMIZED = ON);
GO
DECLARE @VariableName TypeName;
GO

This variable is only stored in memory. In-memory optimized tables and table types use the same data structures, so the data access is going to be more efficient comparing to disk-based table variables.

For more details, please refer to the following MSDN blog post: Improving temp table and table variable performance using memory optimization

Summary

The In-Memory OLTP is a relatively young technology that is designed to work with huge and very busy OLTP systems that support hundreds or even thousands of concurrent users. It was introduced in SQL Server 2014 and evolved in SQL Server 2016.
At the same time, the technology contains a number of restrictions and limitations.
Not all T-SQL features and data types supported by memory-optimized tables, such tables can not contain rows that exceed 8060 bytes, and also do not support
ROW-OVERFLOW and LOB storage. You can not alter tables and indexes (in SQL Server 2014), once the table is created.
Despite this, we are expecting that further versions of In-Memory OLTP will have fewer limitations!

Also Read:

Using Indexes in SQL Server Memory-Optimized Tables