When SQL Server 2016 was released, it brought with it a number of enhancements to the InMemory OLTP feature. One of the most exciting additions was the concept of Operational Analytics, which allowed for a mashup of InMemory OLTP and Clustered ColumnStore Index.

InMemory OLTP was initially introduced as a powerful capability for creating a latch-free environment and achieving faster inserts without locking the table. Its optimistic concurrency model proved to be useful in specific scenarios. On the other hand, the use of Clustered ColumnStore Index allowed for storing data in a columnar format inside SQL Server.

However, prior to SQL Server 2016, there was no way to combine these two features. With the release of SQL Server 2016, we now have the ability to use a Clustered ColumnStore Index on top of an InMemory OLTP table.

Let’s take a look at a typical example:

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

Make sure you run this script on a database that has InMemory capability turned on. If you try to run it on a system database, you will likely encounter an error message stating that memory optimized tables cannot be created in system databases.

Now, you might be wondering how this combination of InMemory OLTP and Clustered ColumnStore Index can benefit you. Well, the answer lies in the ability to achieve both high-performance transactional processing and efficient analytical processing within the same database.

By leveraging the InMemory OLTP capabilities, you can ensure faster inserts and updates without locking the table. This is particularly useful in scenarios where you have high concurrency and need to maintain optimal performance.

On the other hand, the Clustered ColumnStore Index allows for efficient data compression and query performance for analytical workloads. By storing data in a columnar format, you can achieve significant storage savings and faster query execution times.

So, whether you are dealing with transactional workloads or analytical workloads, the combination of InMemory OLTP and Clustered ColumnStore Index can provide you with the best of both worlds.

Are you currently using any of these features in your SQL Server environment? If so, we would love to hear about your use case and how it has benefited your organization. Please share your thoughts and experiences in the comments section below.