If you are ready to use Hekaton then you need to know that there are some limitations, not all data types are supported.
The following list contains the set of supported data types in both Hekaton tables and stored procedures:
bit
All integer types: tinyint, smallint, int, bigint
All money types: money, smallmoney
All floating types: float, real
Date/time types: datetime2, date, time, datetime, smalldatetime
numeric and decimal types
All non-LOB string types: char(n), varchar(n), nchar(n), nvarchar(n), sysname
Non-LOB binary types: binary(n), varbinary(n)
Uniqueidentifier
The data types listed below are unsupported
datetimeoffset
varbinary(max)
varchar(max)
nvarchar(max)
xml
text
image
sql_variant
There is also a limit of 8060 bytes on row size, this includes variable length columns. You cannot create a table with two varchar(5000) columns.
if you try to do the following, you will get an error
CREATE TABLE [dbo].[HekaTest]
( [Col1] varchar(5000),
[Col2] varchar(4000) NOT NULL,
CONSTRAINT [HekaTest_PK] PRIMARY KEY NONCLUSTERED HASH ([Col1]) WITH(BUCKET_COUNT = 1000000)
) WITH (MEMORY_OPTIMIZED = ON,
DURABILITY = SCHEMA_AND_DATA)
Here is the error that you get
_Msg 41307, Level 16, State 1, Line 4
The row size limit of 8060 bytes for memory optimized tables has been exceeded. Please simplify the table definition._
Just be aware of these limitations if you are thinking about using Hekaton. There are also limitations with T-SQL, for example MERGE statement, TRUNCATE statement and identity columns can’t be used, certain functions can’t be used either. Make sure to look at the documentation to see what the restrictions are