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 T-SQL modules

Unsupported Data Types

The following data types are not supported:

datetimeoffset (Transact-SQL)

hierarchyid (Transact-SQL)

sql_variant (Transact-SQL)

geography (Transact-SQL)

rowversion (Transact-SQL)

User-Defined Types

geometry (Transact-SQL)

xml (Transact-SQL)

Notable Supported Data Types

Most data types are supported by the features of In-Memory OLTP. The following few are worth noting explicitly:

String and Binary Types	For more information
binary and varbinary*	binary and varbinary (Transact-SQL)
char and varchar*	char and varchar (Transact-SQL)
nchar and nvarchar*	nchar and nvarchar (Transact-SQL)

For the preceding string and binary data types, starting with SQL Server 2016:

• 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 8060 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

Starting with SQL Server 2016, memory-optimized tables support off-row columns, which allow a single table row to be larger than 8060 bytes. The following Transact-SQL SELECT statement reports all columns that are off-row, for memory-optimized tables. Note that:

• All index key columns are stored in-row.
  • Nonunique index keys can now 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
        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	Memory-Optimized Table Variables

See Also

Transact-SQL Support for In-Memory OLTP
Implementing SQL_VARIANT in a Memory-Optimized Table
Table and Row size in Memory-Optimized Table