Development · Uncategorized

Limitation of In-Memory OLTP in SQL Server 2016 – TRUNCATE table not supported

Hi friends, another limitation that we will discussed is TRUNCATE table command is not supported with 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.

TRUNCATE TABLE T2

But as we run this statement it comes up with an error –

‘The statement ‘TRUNCATE TABLE’ is not supported with memory optimized tables.’

Lim2

So, we got to know limitation of In-memory OLTP is that TRUNCATE table command is not supported in In-memory table. Might be this limitation also 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