SQL Server 2014: Creating Table Optimized for In-Memory OLTP

SQL Server 2014 has a lot of new features over its predecessor. One among them is the built-in ability for In-Memory OLTP (Online Transaction Processing). In the previous article, I wrote about creating In-Memory OLTP compatible database. In this article, I’m writing the steps for creating table optimized for In-Memory OLTP. There are two types of memory OLTP optimized tables. They are durable tables and non-durable tables.

Durable tables are memory optimized tables which resides on the disc just like the regular table which we are using in the SQL Server 2005, 2008 or 2012.

On the other hand non-durable tables are also memory optimized tables, but the data resides on the memory. Only the schema will be there in the disc.The data in the non-durable tables are available till the server is running. When the server is shut-down or restarted, the data will be lost. It’s like a permanent table with temporary data.

Let’s see how to create both the types of memory optimized tables using TSQL.

Creating Durable Table Optimized for In-Memory OLTP:

1. Launch SSMS and open a query window.
2. Select the database you want to create table.
3. Execute the below create table statement. 

   CREATE TABLE [dbo].[MyTecBits_Names_1](
   [Sl_no] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=20000000),
   [Category] [int] NULL,
   [Name] [varchar](30) NULL,
   )
   WITH (MEMORY_OPTIMIZED=ON)
   GO
   

4. The table will be created. This table is optimized for In-memory OLTP. If you see the create statement, there will be a table option MEMORY_OPTIMIZED=ON. This table option determines whether the table is In-Memory OLTP optimized or not.
   

Creating Non-Durable Table Optimized for In-Memory OLTP:

1. The create statement, for creating Non-Durable Table is below.

   CREATE TABLE [dbo].[MyTecBits_Names_2](
   [Sl_no] [int] NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=20000000),
   [Category] [int] NULL,
   [Name] [varchar](30) NULL,
   )
   WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY)
   GO
   

2. Executing this statement will create non-durable memory optimized table. In this create statement, in addition to MEMORY_OPTIMIZED table option, there is another table option DURABILITY. This table option DURABILITY = SCHEMA_ONLY is the one which determines whether the table is Durable or Non-Durable.
   

Reference: TechNet