Memory Optimized Tables and Columnstore

January 26, 2016

Let’s follow up on When ALTER TABLE doesn’t work. To summarize: while messing around with SQL Server 2016 CTP 3.2 I was unable to issue ALTER TABLE statements on a memory optimized table that has a clustered columnstore index.

What Will Work in SQL Server 2016

It turns out that SQL Server 2016 CTP 3.2 doesn’t have full support for this combination of features. As far as what will be working when SQL Server 2016 is released… I don’t have the answer to that question. Here’s what I do know (based on research conducted on 2016-01-19):

Columnstore Indexes Versioned Feature Summary states “For in-memory tables, a columnstore index can only be created when the table is created.”
Columnstore Indexes for Real-Time Operational Analytics states “You cannot add a columnstore index on an existing in-memory table. This limitation will be removed beforeSQL Server 2016 Community Technology Preview 3.2 (CTP 3.2) is released for RTM.”

In theory, we should be able to issue an ALTER TABLE and add a columnstore index to an existing table. But, there’s an important limitation… [caption id=“attachment_975” align=“alignright” width=“300”] Such pretty, well ordered columns![/caption]

What Won’t Work in SQL Server 2016

Even though the product is in flux, there will be some limitations on memory optimized tables that also have a clustered columnstore index. DDL is limited after the clustered columnstore index has been created - what I experienced is by design. If you’re going to be creating a clustered columnstore index on a memory optimized table, make sure you perform any other DDL before you create the index. The upside is that you can now create constraints, foreign keys, and indexes right in the CREATE TABLE statement. So, for many teams, this isn’t that big of a deal.

Working with Memory Optimized Tables and Columnstore

That leaves the question: how can we safely work with columnstore indexes and SQL Server 2016? If you haven’t been using columnstore indexes or memory optimized tables before, tread carefully. Although you can create a columnstore index wherever you want, the columnstore index limits how you can modify a memory optimized table. Consider starting a proof of concept process where you built a demo feature and work with data volumes that exceed your current data volume. If you’ve already been using columnstore indexes or memory optimized tables, you’re in luck - you can now combine these two features. Teams that have been using either feature should be excited - it’s easier than ever to get a lot of mileage out of high performance features in SQL Server. These teams should, obviously, test this feature first. But if you’ve been using memory optimized tables, you’re already familiar with performing DDL shenanigans.

What’s It All Mean?

Well, what this really means is that you still need to carefully consider each new feature before you implement it. Not all decisions are easy to walk away from. But, if you’re willing to work within the limitations, you’ve got yourself a great feature that lets you perform fast OLTP and analysis, all on the same data structure.

“Capitol Arboretum Columns - HDR” by Nicolas Raymond is licensed with CC BY 2.0.