Posted in 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

Author:

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 http://www.sqlservercentral.com/blogs/kapil-blogs/

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 )

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