Edit

Share via

Supported data types for In-Memory OLTP

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

This article lists the data types that are unsupported for the In-Memory OLTP features of:

  • Memory-optimized tables
  • Natively compiled Transact-SQL (T-SQL) modules

Unsupported data types

The following data types aren't supported:

Notable supported data types

In-Memory OLTP features support most data types. The following list is worth noting explicitly:

String and binary types For more information
binary and varbinary binary and varbinary
char and varchar char and varchar
nchar and nvarchar nchar and nvarchar

For the preceding string and binary data types, starting with SQL Server 2016 (13.x):

  • An individual memory-optimized table can also have several long columns such as nvarchar(4000), even though their lengths would add to more than the physical row size of 8,060 bytes.

  • A memory-optimized table can have max length string and binary columns of data types such as varchar(max).

Identify LOBs and other columns that are off-row

In SQL Server 2016 (13.x) and later versions, memory-optimized tables support off-row columns, which allow a single table row to be larger than 8,060 bytes. The following Transact-SQL SELECT statement reports all columns that are off-row, for memory-optimized tables:

  • All index key columns are stored in-row.
    • Nonunique index keys can include nullable columns on memory-optimized tables.
    • Indexes can be declared as UNIQUE on a memory-optimized table.
  • All LOB columns are stored off-row.
  • A max_length of -1 indicates a large object (LOB) column.
SELECT OBJECT_NAME(m.object_id) AS [table],
       c.name AS [column],
       c.max_length
FROM sys.memory_optimized_tables_internal_attributes AS m
     INNER JOIN sys.columns AS c
         ON m.object_id = c.object_id
        AND m.minor_id = c.column_id
WHERE m.type = 5;

Other data types

Other types For more information
table types Faster temp table and table variable by using memory optimization

Related content

  • Last updated on