Hi friends, in this blog I will discuss another limitation of In Memory OLTP in SQL Server 2016.
Once you create a table you cannot create any index created on memory optimized table using CREATE INDEX statement. So to create indexes you need to create them with column definition of memory optimized table.
Let’s create a memory optimized table with index on column Name.
CREATE TABLE Test_OLTP
Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),
) WITH (Memory_Optimized = ON, DURABILITY = SCHEMA_AND_DATA);
After creation of the table I also want to create a nonclustered index on column ‘CompanyName’.
Let’s execute the below query to create a nonclustered index-
Create nonclustered index ix_companyname ON Test_OLTP(CompanyName)
When I run this query I got an error –
Msg 10794, Level 16, State 13, Line 4
The operation ‘CREATE INDEX’ is not supported with memory optimized tables.
So, we cannot alter the memory optimized tables once created as SQL Server stores the structure of memory optimized tables as metadata.
To alter the table either recreate the table or create another table.
That’s all for the day 🙂
Have a nice day!!