Supported Data Types for In-Memory OLTP


Updated: May 27, 2016

Applies To: SQL Server 2016

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure 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 stored procedures

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.

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

String and Binary TypesFor 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

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.
        OBJECT_NAME(m.object_id) as [table],                   as [column],
             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
        m.type = 5;

Natively compiled modules support for LOBs

When you use a built-in string function in a natively compiled modules, such as a native proc, the function can accept a string LOB type. For example, in a native proc, the LTrim function can input a parameter of type nvarchar(max) or varbinary(max).

These LOBs can be the return type from a natively compiled scalar UDF (user-defined function).

Other Data Types

Other TypesFor more information
table typesMemory-Optimized Table Variables

Transact-SQL Support for In-Memory OLTP
Implementing LOB Columns in a Memory-Optimized Table
Implementing SQL_VARIANT in a Memory-Optimized Table

Community Additions