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.

CREATE TABLE Test_OLTP

(

       Name varchar(40) NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH(BUCKET_COUNT =10000),

       Place varchar(30),

       CompanyName varchar(40),

       Skill varchar(100)

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

Lim_createindex

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

 

 

Advertisements

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: http://www.insidesql.org/blogs/andreaswolter/2016/07/the-sql-server-2016-in-memory-evolution-from-row-store-to-columnstore-to-in-memory-operational-analytics
    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”. (http://www.sqlservergeeks.com/summit2016/pre-conference-seminars/)
    I’d be happy to see you or some of your readers there.
    Andreas

    Like

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