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:
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