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.
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:
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 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.
This is Jeremiah
I live in Portland, OR. I have two dogs.
I recently received a Master's of Science in Computer Science from Portland State University.
I'm was Microsoft MVP from 2009 - 2018 with a pile of certifications. Somewhere along the way, I wrote a database client for Riak and then handed it off to the community.