SQL SERVER – Creating Clustered ColumnStore with InMemory OLTP Tables

When SQL Server 2016 was released, there were a number of enhancements that were discussed around how InMemory OLTP removed which were as limitations. I personally saw some real boot to some of the capabilities coming into InMemory OLTP especially around the concept called as Operational Analytics that Microsoft calls in this release.

In Memory OLTP when it was first introduced was an awesome capability for a latch free environment and fastest inserts without locking the table. This optimistic concurrency model was useful in specific areas. The use of clustered Columnstore inside SQL Server was the ability to store data in columnar format inside SQL Server. Though these two capabilities existed, there was no mashup of these features till SQL Server 2016.

Now inside SQL Server we have the ability to use a Clustered ColumnStore Index on top of an InMemory OLTP table. A typical example of the same is shown below.

Here is the script I used in the above image for your reference.

CREATE TABLE tbl_my_InMemory_CCI (  
    my_Identifier INT NOT NULL PRIMARY KEY NONCLUSTERED,  
    Account NVARCHAR (100),  
    AccountName NVARCHAR(50),  
    ProductID INT,
	Quantity INT,  
    INDEX account_Prod_CCI CLUSTERED COLUMNSTORE  
    )  
    WITH (MEMORY_OPTIMIZED = ON);  
GO

Make sure you run this on a database that has InMemory capability turned on. If you try to run it on a system database, you are likely to get an error like:

Msg 41326, Level 16, State 1, Line 1
Memory optimized tables cannot be created in system databases.

I am sure you will be aware of this, but since I learnt this – thought this was worth a share. Do let me know if you are using any of the features of InMemory or ColumnStore inside SQL Server? What is your use case for the same? Do let me know via comments.

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