Now that we have a filegroup/database that is ready for high-performance workloads, we need to take a moment and consider what limitations there are with the In-Memory OLTP engine.

The following data types are supported:

• All integer types—tinyint, smallint, int, and bigint
• All float types—float and real
• All money types—smallmoney and money
• Numeric and decimal
• All non-LOB string types—char(n), varchar(n), nchar(n), nvarchar(n), and sysname
• Non-LOB binary types—binary(n) and varbinary(n)
• Date/time types—smalldatetime, datetime, date, time, and datetime2
• Unique identifier

This leaves out the LOB data types, that is, XML, max types (for example, varchar(max)) and CLR types (remembering that this is valid for the initial implementation in SQL Server 2014). This also includes row lengths exceeding 8,060 bytes. This limit is generally not recommended, as it will cause issues even in regular tables. However, with memory-optimized tables we cannot even create a table whose row length exceeds 8,060 bytes.

Other than the data type limitations, the following restrictions also apply:

• No FOREIGN KEY or CHECK constraints
• No UNIQUE constraints (except for PRIMARY KEY)
• No DML Triggers
• A maximum of eight indexes (including the PRIMARY KEY index)
• No schema changes after table creation

More specifically to the last restriction, absolutely no DDL commands can be issued on a memory-optimized table: no ALTER TABLE, CREATE INDEX, or ALTER INDEX, DROP INDEX. Effectively, once you create a memory-optimized table, it is unchangeable.

So, with the bad news out of the way, let's create our first memory-optimized table. But before we do, we must note that memory-optimized tables cannot be created in system databases (including tempdb). If we attempt to do so, we will receive an error message:

Msg 41326, Level 16, State 1, Line 43
Memory optimized tables cannot be created in system databases.

The following code will therefore change the connection into the previously created InMemoryTest database and then create a test memory-optimized table:

 USE InMemoryTest
GO
CREATE TABLE InMemoryTable 
( 
   UserId INT NOT NULL PRIMARY KEY NONCLUSTERED, 
   UserName VARCHAR(255) NOT NULL, 
   LoginTime DATETIME2 NOT NULL, 
   LoginCount INT NOT NULL, 
CONSTRAINT PK_UserId PRIMARY KEY NONCLUSTERED (UserId),  INDEX NCL_IDX HASH (UserName) WITH (BUCKET_COUNT=10000) 
    
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY=SCHEMA_AND_DATA) 
GO 

Let's consider a few of the previous lines of code. Firstly, the table creation command resembles that of many other tables that can be created in SQL Server. We are not confronted with anything unfamiliar. Only in the last two lines of code do we notice anything peculiar. We have the previously mentioned hash index, with a bucket count of 10,000 on the UserName column. The last line of code has two new keywords: MEMORY_OPTIMIZED=ON, is simple enough— we are informing SQL Server that this table should be created as a memory-optimized table. However, the DURABILITY keyword is something that we have only tangentially mentioned so far.

The durability options available to us are either SCHEMA_AND_DATA or SCHEMA_ONLY. These keyword options are clear enough to understand. Either the schema and the data will be recovered after a server restart, or only the schema. With SCHEMA_ONLY we have the ability to completely bypass writing data changes to the transaction log, because our memory-optimized table has no requirements in terms of data recovery. This has a major positive performance impact if the data in a SCHEMA_ONLY table meets the requirement of not needing recovery.

After making our decisions about columns, indexes, and the durability of our table, we issue the CREATE TABLE command. At this point, SQL Server receives the command and generates the table as a memory-optimized object. This results in a compiled DLL being created for the memory-optimized table, including the compiled access methods for the memory-resident object. This compilation process is the main factor in the limitations listed previously, especially the reason for alterations of the table and indexes after creation.