Posted in Development

Limitation of In-Memory OLTP in SQL 2016 – Create Index is not supported

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.




       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)


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!!





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

One thought on “Limitation of In-Memory OLTP in SQL 2016 – Create Index is not supported

  1. Hello Kapil Singh Kumawat,
    in SQL Server 2016 there is the option of ALTER TABLE … ADD INDEX… for memory-optimized tables. Reason it cannot be a separate “CREATE INDEX” is that Indexes are part of the tables definition.
    But you do not have to Re-Create the table.
    In my recent blog-post you can see an example:
    Btw: I will be coming to India in August for the SQL Server Geeks Conference in Bangalore and give a full-day PreCon on the topic “The Present and Future: In-Memory in SQL Server – from 0 to Operational Analytics Master”. (
    I’d be happy to see you or some of your readers there.


Leave a Reply

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

You are commenting using your 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