SQL SERVER – Creating Clustered ColumnStore with InMemory OLTP Tables

SQL
3 Comments

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.

SQL SERVER - Creating Clustered ColumnStore with InMemory OLTP Tables inmemory-cci-01-800x430

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)

, , , ,
Previous Post
SQL SERVER – Unable to Bring SQL Online – DoSQLDataRootApplyACL : Failed to Create Directory Tree at SQLDataRoot
Next Post
SQL SERVER – Unable to Attach Database – File Activation Failure – The Log Cannot be Rebuilt

Related Posts

3 Comments. Leave new

  • Is this supposed to be an older post? The blog says “later this month in October at PASS” and we are already in November now.

    Reply
  • Is InMemmory OLTP appropriate for database with many writes?
    Or InMemmory OLTP is better for database with many reads?

    Reply

Leave a Reply

Menu