Posted in Development, Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – LOB datatypes not supported

Hi friends, Wish you all a very Happy New Year. This is my first blog of 2016 and starting it with SQL 2016 J . Now a days I am exploring In-Memory features and changes done in SQL 2016 CTP versions. I have installed CTP 3.0 currently and comparing changes done in SQL 2016 from SQL 2014 In-memory features.

For next few blogs I will try to cover the limitation of In-Memory OLTP feature.

First limitation that we will discussed is support of LOB datatypes. LOB datatypes like varchar(max), nvarchar(max), XML etc. are not supported in memory optimized tables.

Let’s see a demo on this.

First we will create a memory optimized table.



[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH


[City] varchar(32) not null,

[State_Province] varchar(32) not null,

[LastModified] datetime not null,

INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])



Once the table gets created, we will try to add one more column with LOB datatype.


ADD Description nvarchar(max) NULL

Alter table T2

ADD Description XML

But the column does not gets added and comes up with an error –

‘The type ‘nvarchar(max)’ is not supported with memory optimized tables.’


So, we got to know limitation of In-memory OLTP is that LOB datatypes are not supported in In-memory table. Might be this limitation gets over with future CTP versions of SQL Server 2016.

Have a happy learning.



I am Kapil Singh Kumawat working on SQL Server since last 5 years. I am from Jaipur, Rajasthan, India and currently working in Cognizant Technology Solutions as SQL Server Developer. I have a good experience in writing queries, performance tuning, SSIS, SSAS, Power BI, Data migration and database designing. Apart from database I have interest in travelling,watching football and listening music. My blogs are also published on

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s