When ALTER TABLE doesn't work

We’re all used to being able to change things whenever we want. Heck, I put on a different pair of socks just to write this blog post! They’re my bloggin’ socks. But, back to the topic at hand, we’re used to being able to use ALTER TABLE commands to change tables in SQL Server. And, in fact, most of the time that just works.

In-Memory OLTP and ALTER TABLE

Way back in SQL Server 2014, when we first got In-Memory OLTP, it wasn’t possible to change a table once you’d created it. Instead you had to:

  • Create a new, identical, table.
  • Copy data into the new table.
  • Drop the original table.
  • Create a new table with the same name as the original table.
  • Copy data into the new/original table.
  • Drop the copied table.
  • Have enough memory for all of this to work.

That’s a lot of work, but it’s theoretically not that tricky for an experienced DBA. Plus, savvy DBAs could probably automate this if they really wanted to. Thankfully, ALTER TABLE works in SQL Server 2016 on In-Memory OLTP tables. At least, according to SQL Server Books Online. And, for the most part, it does work. You can use  ALTER TABLE to add nonclustered indexes to existing tables, to add columns, or to do just about everything that you’d want to do.

Just About… ALTER TABLE

What’s the “just about everything” for? One of the big features in SQL Server 2016 is that it’s possible to put a columnstore index on a memory optimized table. ZOMGWTFBBQ?!!!!!!1!shiftone!1!!!! Yeah, you heard me. You can basically wear a pair of sports cars like rollerskates with SQL Server 2016. But there’s a catch. A big, smelly, gas guzzling, crippling catch: [caption id=“attachment_969” align=“aligncenter” width=“600”]ALTER TABLE failing on a memory optimized table with a clustered columnstore index Say ALTER TABLE one more time, I dare you![/caption]

wat

Once you’ve put a clustered columnstore index on a memory optimized table, you’re done. You’ll need to define all of your DEFAULTs, foreign keys, unique constraints, etc during table creation. Thankfully, this is all supported by SQL Server 2016’s [CREATE TABLE](https://msdn.microsoft.com/en-us/library/ms174979.aspx) statement. If you’re thinking about implementing In-Memory OLTP to take advantage of falling memory prices, be very careful during your initial design. If you think that you’ll need to run analytical queries against the memory optimized tables, you may find that index tuning changes from a simple process to dance that goes “drop create rinse repeat”. Note: Columnstore indexes on memory optimized tables is specifically called out as being functional in SQL Server 2016 CTP 3.2, which I’m using.