Temporal Tables, Partitioning, and ColumnStore Indexes

This post is mostly a thought experiment

The thought was something along the lines of: I have a table I want to keep temporal history of. I don’t plan on keeping a lot of data in that table, but the history table can accumulate quite a bit of data. I want to partition the history table for easy removal of outdated data, and I want to use ColumnStore indexes because I’m just so bleeding edge all of my edges are bleeding edges from their bloody edges.

Fair warning here

This post assumes you’re already familiar with temporal tables, Partitioning, and ColumnStore indexes. I’m not going to go into detail on any of the subjects, I’m just walking through implementation. If you’re interested in temporal tables, Itzik Ben-Gan has a two part series here and here. We have a list of great Partitioning resources here, and of course, Niko Neugebauer has a (so far) 80 part series on ColumnStore over here.

On to the experiment!

The hardest part was getting the ColumnStore index on the history table. Let’s look at the process. There’s a lot of braindumping in the code. Feel free to skip the setup stuff, if you don’t care about it.

Wew

If you’re here, I should make a couple notes. Microsoft added a really cool feature to Temporal Tables recently: The ability to mark them as hidden. This is gravy for existing apps and tables, because you don’t have to store the row versioning data along with all your other data. It would be really nice if they’d add valid date ranges (read: expiration dates) to the syntax, but hey, maybe in the next RC…

I explicitly named our history table, because SQL will name it something horrible and dumb if you don’t. You don’t have much control over History table creation or indexing at conception, but you can make changes afterwards. SQL will drop a clustered index on your table that mirrors the clustered index definition of the base table.

ColumnStore Party!

So let’s see here. I have a base table. I have a history table. I have a Partitioning Scheme and Function. How does one get their history table to Partitioned and ColumnStored status? With a few catches!

First, you have to drop the index on the history table:

The first thing I tried was just creating my Clustered ColumnStore index in place:

But that throws an error!

Msg 35316, Level 16, State 1, Line 119 The statement failed because a columnstore index must be partition-aligned with the base table.

For reference, trying to create a nonclustered ColumnStore index throws the same error.

The next thing I did was create a nonclustered index, just to make sure I could create something aligned with the Partitioning. That works!

Please and thank you. Everyone’s a winner. But can you create ColumnStore indexes now?

Nope. Same errors as before. Clearly, we need a clustered index here to get things aligned. The problem is, you can’t have two clustered indexes, even if one is ColumnStore and the other isn’t.

Msg 35372, Level 16, State 3, Line 121 You cannot create more than one clustered index on table ‘dbo.RockwellHistory’. Consider creating a new clustered index using ‘with (drop_existing = on)’ option.

Ooh. But that DROP_EXISTING hint! That give me an idea. Or two. Okay, two ideas. Either one works, it just depends on how uh, bottom-side retentive you are about how things are named. This will create a ColumnStore index over your clustered index, using DROP_EXISTING.

This will drop your current Clustered Index, and create your Clustered ColumnStore index in its place, just with a name that lets you know it’s ColumnStore. Hooray. Hooray for you.

SUCCESS!

Never tasted so… Obtuse, I suppose. Maybe like the parts of a lobster you shouldn’t really eat. Anyway, I hope this solves a problem for someone. I had fun working out how to get it working.

I can imagine more than a few of you seeing different ways of doing this through the course of the article, either by manipulating the initial index, creating the history table separately and then assigning it to the base table, or using sp_rename to get the naming convention of choice. Sure, that’s all possible, but a lot less fun.

Thanks for reading!

Brent says: when Microsoft ships a feature, they test its operability. When you use multiple new features together, you’re testing their interoperability – the way they work together. Microsoft doesn’t always test – or document – the way every feature works together. For example, in this example, if you want to play along as a reader, your next mission is to look at query plans that span current and history data, see how the data joins together, and how it performs.

Previous Post
SQL Interview Question: “Tell me what you see in this screenshot.”
Next Post
Spring Cleaning Your Databases

7 Comments. Leave new

  • Excellent post! Thanks – I’d recently posted about using temporal tables with filegroups and compression, so the next logical step. And temporal tables are listed as a standard edition feature which is great news!

    Reply
  • Brilliant thank you! Looking at this and some other documentation I see we can also use our own custom scripts to clean up the data.

    Is there a way to prepopulate the temporal table with historic data and replace old history / audit tables by inserting this data into the temporal tables?

    Reply
  • I haven’t tried that, so maybe try asking your question over on dba.stackexchange.com

    Reply
  • Thanks I shall do 🙂

    Reply
  • I haven’t been doing anything with temporal tables, but just had the experience of introducing a clustered columnstore index to a partitioned table that was previously a heap. I replaced a non-clustered CS index with the clustered one and found that by using the DROP_EXISTING = ON clause I didn’t have to include the partitioning information in the query – it picked it up from the table definition.

    Reply
  • Heya,

    After encountering the error “Msg 35316, Level 16, State 1, Line 119 The statement failed because a columnstore”, and re-reading your post and trying to create this partitioned clustered columnstore index using multiple approaches, I can only say as bottom line that a table being a heap it is non-clustercolumnstoreable (tots made up). If that’s put in the post it would help a lot!

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}