SQL SERVER – Fix Error Msg 10794, Level 16 – The operation ‘CREATE INDEX’ is not supported with memory optimized tables.

SQL
2 Comments

Whenever I write something as a new concept, I do see people reading the blog do give it a try. Many a times I get queries that the script didn’t work for them after they learnt the concepts about memory optimized tables. The blog I wrote on how In Memory OLTP was supporting ColumnStore – read it: SQL SERVER – Creating Clustered ColumnStore with InMemory OLTP Tables

After reading this blog, I saw a note on my Inbox that the feature was not actually working as expected. I was surprised that he wrote back. I asked what was the error to get more details. He was getting an Error 10794. And immediately I wanted to see the complete error because offlate I have seen this the error messages from Microsoft are elaborate in general, and I was not proved wrong.

Msg 10794, Level 16, State 13, Line 15
The operation ‘CREATE INDEX’ is not supported with memory optimized tables.

Now, as soon as the message was sent, I understood what he was doing. To replicate this error, I wrote the following script for reference:

--Create the table
CREATE TABLE tbl_my_InMemory (
	my_Identifier INT PRIMARY KEY NONCLUSTERED 
        HASH WITH (BUCKET_COUNT = 100000),  
    Account NVARCHAR (100),  
    AccountName NVARCHAR(50),  
    ProductID INT,
	Quantity INT  
)WITH
(
    MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_ONLY
);
GO
-- Create a Clustered ColumnStore Index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_tbl_my_InMemory 
ON tbl_my_InMemory

SQL SERVER - Fix Error Msg 10794, Level 16 - The operation 'CREATE INDEX' is not supported with memory optimized tables. error-cci-inmemory-800x264

As you can see clearly, based on the previous blog, the Clustered ColumnStore Index was created inline. Currently in this release of SQL Server, Microsoft has not given the capability to add the Clustered ColumnStore Index after the table has been created.

I am sure at this moment, there are some restrictions of use, but there is a lot to learn from each other too. Do share anything you face when working with these features. Would love to blog them too.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Cannot Show Requested Dialog – Property Size is Not Available for Database
Next Post
SQL SERVER – Added New Node in Windows Cluster and AlwaysOn Availability Databases Stopped Working

Related Posts

2 Comments. Leave new

  • Pinal, it is absolutely possible to create indexes that are not inline, for memory-optimized tables, but you cannot create any type of index for in-mem tables using “CREATE INDEX”. You must use “ALTER TABLE” instead.

    This will work, but only if your memory-optimized table is durable:
    ALTER TABLE tbl_my_InMemory
    ADD INDEX CCI_tbl_my_InMemory CLUSTERED COLUMNSTORE

    Reply

Leave a Reply

Menu