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