Supported Data Types for In-Memory OLTP

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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) geography (Transact-SQL) geometry (Transact-SQL)
hierarchyid (Transact-SQL) rowversion (Transact-SQL) xml (Transact-SQL)
sql_variant (Transact-SQL) User-Defined Types .

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