Creating Memory-Optimized Table in SQL Server 2014

http://www.sqlservercentral.com/articles/memory-optimized+table/110748/

SQL Server 2014 was released on April 1^st. One of the most exciting features is its new In-Memory technology. Today I’m going to give you a quick introduction about memory-optimized table in SQL Server 2014 and its limitations.

Creating a memory-optimized table

First of all, let’s create a test database – DB1:

USE master;
GO
CREATE DATABASE DB1;
GO

The next step would be creating a required file group for memory-optimized tables. To do that, we need to add a memory-optimized file group to DB1:

ALTER DATABASE DB1 ADD FILEGROUP IMOLTP CONTAINS MEMORY_OPTIMIZED_DATA;
GO

Notice the key word “MEMORY_OPITIMIZED_DATA”, it tells SQL Server this file group is in memory and will store memory-optimized objects.

Now let’s add a file into this file group:

ALTER DATABASE DB1 ADD FILE (NAME='IMOLTP',FILENAME='C:\DB\IMOLTP') TO FILEGROUP IMOLTP;
GO

This statement is similar to the statement that adds a Filestream file to SQL Server. The filename specified here is a folder name instead of a filename. The difference between these two is that adding memory-optimized files don’t need to turn on Filestream feature in SQL Server. In the meantime, it’s possible to add multiple memory-optimized data files for greater performance.

Once the memory-optimized data file is ready, we can go ahead and create our first memory-optimized table:

USE DB1;
GO
CREATE TABLE T1_OLTP
( ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000)
, MSG CHAR(8000)) WITH (MEMORY_OPTIMIZED = ON)
GO

Notice that 2 places in the previous statement are different from the traditional version:

1. “MEMORY_OPTIMIZED = ON” at the end of the table definition
2. Nonclustered hash index in the table definition

By having “MEMORY_OPTIMIZED = ON”, we are telling SQL Server that this is a memory-optimized table. We don’t need tell the memory-optimized file group name, SQL Server will decide where this table will be stored.

Unlike traditional disk-based tables, memory-optimized tables support two types of indexes: non-clustered hash indexes and non-clustered indexes. To be able to create a memory-optimized table, at least one index needs to be created. We have created a hash non-clustered index in our demo table. The bucket count of the hash index must be specified, the value of the bucket count is recommended to be greater than the number of unique keys.

A primary key is also required because memory-optimized tables don’t support heap tables.

In SQL Server 2014, memory-optimized tables can be durable or non-durable. Durable memory-optimized tables are like disk-based tables, data is persisted on disk. Non-durable tables, on the other side, will only keep table schema. Data stored in non-durable tables is persisted on disk and will be lost after server restart. Non-durable tables can be useful for temporary tables or staging tables which data persistence is not required. By default, memory-optimized tables are durable.

To create a durable memory-optimized table, use DURABILITY = SCHEMA_AND_DATA :

CREATE TABLE T2_OLTP
(
  ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000)
, MSG CHAR(8000)
)
WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA)
GO

Or use DURABILITY = SCHEMA_ONLY for a non-durable table:

CREATE TABLE T3_OLTP
(
  ID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000)
, MSG CHAR(8000)
)
WITH (MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY)
GO

In our T1_OLTP table, DURABILITY is not mentioned and SQL Server will create a durable table using its default setting - SCHEMA_AND_DATA.

Now the demo memory-optimized table is ready, let’s do some simple CRUD (Create, Read, Update and Delete) operations:

INSERT INTO T1_OLTP VALUES (1,REPLICATE('A',8000))
GO
SELECT * FROM T1_OLTP
GO
UPDATE T1_OLTP SET MSG =REPLICATE('B',8000)
GO
SELECT * FROM T1_OLTP
GO
DELETE FROM T1_OLTP
GO
SELECT * FROM T1_OLTP
GO

As you can see, inserting/updating/deleting data using memory-optimized table are same as we do with disk-based tables, just faster in some cases.

Limitations

The Memory-Optimized table is a new feature in SQL Server 2014 and it comes with limitations. I have listed some limitations below:

Unsupported feature	Workaround
Replication	Drop replication
Computed column	Use regular column and include computed column value with insert/update in stored procedure
Trigger	Customize inclusive stored procedure to replace native insert/update/delete
Foreign Key	Customize inclusive stored procedure to replace native insert/update/delete
Check Constraint	Customize inclusive stored procedure to replace native insert/update/delete
Unique Constraint	Customize inclusive stored procedure to replace native insert/update/delete
Alter table	Drop and recreate
Create Index	All indexes need to be created in Create Table
Merge (as Target)	Use Insert/Update/Delete
Nullable column	Change to Non-Nullable column and change application logic accordingly
Identity*	Use Sequence instead

*   Identity(1,1) is supported

For a complete list of unsupported features, visit BOL.

In the meantime, some data types are also unsupported:

• datetimeoffset
• geography
• geometry
• hierarchyid
• varchar(max)
• nvarchar(max)
• varbinary(max)
• xml
• text
• ntext
• sql_variant
• rowversion
• user-defined types.

More details can be found on BOL.

You might have seen some table definitions like the one below in SQL Server 2008 or up:

CREATE TABLE T1

  (

    id INT

  , msg VARCHAR(5000)

  , msg2 VARCHAR(5000)

  )

It’s acceptable for a table to have a total row size of variable length columns greater than 8K. In SQL Server 2014, this is still valid for disk-based tables, but memory-optimized table will not accept it. If we run

CREATE TABLE T2_OLTP

  (

    ID INT NOT NULL

           PRIMARY KEY NONCLUSTERED HASH WITH ( BUCKET_COUNT = 1000000 )

  , MSG VARCHAR(5000)

  , MSG2 VARCHAR(5000)

  )

  WITH (

       MEMORY_OPTIMIZED =

       ON)

GO

The following error will be raised:

Zoom in  |  Open in new window

In this case, we will have no choice but to redefine the table schema.

Although memory-optimized tables seem to have many limitations, it is very possible Microsoft will enhance its In-Memory technology and remove many of the constraints in the future releases.

Summary

In this article, we discussed how to create memory-optimized tables, the limitations and workarounds. Hopefully this will help you in implementing memory-optimized tables in your future projects