Database Reference
In-Depth Information
Listing 32-1.
Creating a database with the in-memory OLTP filegroup
create database [HekatonDB] on
primary
(name = N'HekatonDB', filename = N'M:\HekatonDB.mdf'),
filegroup [OnDiskData]
(name = N'Hekaton_OnDisk', filename = N'M:\Hekaton_OnDisk.ndf'),
filegroup [InMemoryData] contains memory_optimized_data
(name = N'Hekaton_InMemory', filename = N'S:\HKData\Hekaton_InMemory')
log on
(name = N'HekatonDB_log', filename = N'L:\HekatonDB_log.ldf')
Memory-Optimized Tables
Even though the creation of memory-optimized tables is very similar to the creation of on-disk tables and can be done
with a regular
CREATE TABLE
statement, SQL Server works very differently with memory-optimized tables. Every time
a memory-optimized table is created, SQL Server generates and compiles a DLL that is responsible for manipulation
of table row data. The in-memory OLTP engine is generic, and it does not access or modify row data directly. Rather,
it calls DLL methods instead.
As you can guess, this approach adds limitations on the alterability of the table. Alteration of the table would
require SQL Server to recreate a DLL and change the format of data rows, which is a very time- and resource-consuming
operation. It is not supported, and the schema of a memory-optimized table is static and cannot be altered in any way
after it is created. The same is true for indexes. SQL Server requires you to define indexes inline in a
CREATE TABLE
statement. You cannot add or drop an index or change an index's definition after a table is created.
■
Tip
you can drop and recreate a memory-optimized table to change its definition and/or indexes.
Indexes on memory-optimized tables are not persisted on-disk. SQL Server recreates them at the time when
it starts the database and loads memory-optimized data into memory. Even though the index creation process is
efficient and relatively fast, it still takes time. Usually, this time overhead is not an issue because I/O performance
becomes a limiting factor during the data-loading process. However, indexes use system memory. Contrary to on-disk
table data, which is loaded to the buffer pool on-demand, all data from memory-optimized tables and indexes are
present in memory all of the time. Remember this behavior, and do not add unnecessary indexes to tables.
Memory-optimized tables support at most eight indexes, and they should have a unique
PRIMARY KEY
constraint
defined. Moreover, memory-optimized tables have several limitations as compared to on-disk tables. None of the
following objects are supported:
FOREIGN KEY
constraints
CHECK
constraints
UNIQUE
constraints or indexes with exception of the
PRIMARY KEY
DML
triggers
IDENTITY
columns with
SEED
and
INCREMENT
different than (1,1)
Listing 32-2 shows the code that creates a memory-optimized table. You can define a table as memory-optimized
by specifying the
MEMORY_OPTIMIZED=ON
option of
CREATE TABLE
statement. Ignore index properties for now; we will
discuss them later in this chapter.
