When it comes to optimizing performance in SQL Server, there are various techniques and features that can be utilized. One such feature is the use of memory-optimized tables, which can significantly improve query performance by storing data in memory rather than on disk.

In a recent blog post, I discussed the concept of memory-optimized tables and how they can be used in conjunction with Clustered ColumnStore Indexes. However, it seems that some readers have encountered issues when trying to implement this feature.

One reader reached out to me after encountering an error (Error 10794) while attempting to create a Clustered ColumnStore Index on a memory-optimized table. The error message clearly stated that the operation ‘CREATE INDEX’ is not supported with memory-optimized tables.

To better understand the issue, I asked the reader for more details and was able to replicate the error using the following script:

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

-- Create a Clustered ColumnStore Index
CREATE CLUSTERED COLUMNSTORE INDEX CCI_tbl_my_InMemory ON tbl_my_InMemory;

As you can see, the script attempts to create a Clustered ColumnStore Index on a memory-optimized table. However, in the current release of SQL Server, Microsoft has not provided the capability to add a Clustered ColumnStore Index after the table has been created.

While this limitation may be frustrating for some users, it’s important to remember that SQL Server is a complex and evolving platform. New features and enhancements are constantly being introduced, and it’s essential to stay up-to-date with the latest releases and documentation.

If you encounter any issues or have any questions when working with memory-optimized tables and Clustered ColumnStore Indexes, I encourage you to share your experiences. By sharing our knowledge and learning from each other, we can continue to improve our understanding and utilization of these powerful features.

Thank you for reading, and stay tuned for more informative blog posts on SQL Server!