Supported Data Types for In-Memory OLTP
This article lists the data types that are unsupported for the In-Memory OLTP features of:
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
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|