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.
CREATE TABLE T2
[Name] varchar(32) not null PRIMARY KEY NONCLUSTERED HASH
WITH (BUCKET_COUNT = 100000),
[City] varchar(32) not null,
[State_Province] varchar(32) not null,
[LastModified] datetime not null,
INDEX T1_ndx_c2c3 NONCLUSTERED ([City],[State_Province])
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
Once the table gets created, we will try to add one more column with LOB datatype.
ALTER TABLE T2
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.