Supported Data Types for In-Memory OLTP
Updated: May 27, 2016
Applies To: SQL Server 2016
This article lists the data types that are unsupported for the In-Memory OLTP features of:
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 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
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;
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 Types||For more information|
|table types||Memory-Optimized Table Variables|