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.

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.’

Lim1

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.

Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s