Temporal Tables, Partitioning, and ColumnStore Indexes

Last Updated 7 years ago

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.

--Sample For President 2016
CREATE DATABASE [Sample2016]

--Let's not make a big deal out of this
ALTER DATABASE Sample2016 SET RECOVERY SIMPLE

--Netflix and chill
USE [Sample2016];

--Make sure this stuff is gone
IF OBJECT_ID('dbo.Rockwell') IS NOT NULL
BEGIN
ALTER TABLE [dbo].[Rockwell] SET (SYSTEM_VERSIONING = OFF);
DROP TABLE [dbo].[Rockwell]
DROP TABLE [dbo].[RockwellHistory]
END 

IF (SELECT [ps].[name] FROM [sys].[partition_schemes] AS [ps] WHERE [ps].[name] = 'TemporalDailyScheme') IS NOT NULL
BEGIN
DROP PARTITION SCHEME [TemporalDailyScheme];
END

IF (SELECT [pf].[name] FROM [sys].[partition_functions] AS [pf] WHERE [pf].[name] = 'TemporalDailyPFunc') IS NOT NULL
BEGIN
DROP PARTITION FUNCTION [TemporalDailyPFunc];
END

/*I always feel like...*/
/*Somebody's watching me...*/
CREATE TABLE [dbo].[Rockwell]
(
[ID] BIGINT,
[ProductName] NVARCHAR(50) NOT NULL,
[Price] DECIMAL(18,2) NOT NULL,
[Description] VARCHAR(8000) NOT NULL,
[AuditDateStart] DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
[AuditDateEnd] DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
PERIOD FOR SYSTEM_TIME ([AuditDateStart], [AuditDateEnd]),
CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED ([ID]) 
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[RockwellHistory]) )

/*Partitioning Crap*/

--Make a partitioning function
DECLARE @sql NVARCHAR(MAX) = N'', @i INT = -366, @lf NCHAR(4) = NCHAR(13) + NCHAR(10)
SET @sql = N'CREATE PARTITION FUNCTION TemporalDailyPFunc (DATETIME2(2)) AS RANGE RIGHT FOR VALUES ('
WHILE @i < 0
BEGIN
SET @sql += @lf + 'DATEADD(DAY, ' + CAST(@i AS NVARCHAR) + ', ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) + N' )' + N', '
SET @i += 1
END
SET @sql += @lf + 'DATEADD(DAY, ' + CAST(@i AS NVARCHAR) + ', ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) + N' )' + N');';
EXEC sp_executesql @sql;
--PRINT @sql
GO

--I don't have a ton of filegroups, I just want everything on Primary
CREATE PARTITION SCHEME TemporalDailyScheme
AS PARTITION TemporalDailyPFunc
ALL TO ( [PRIMARY] );

--Sample For President 2016

CREATE DATABASE [Sample2016]

--Let's not make a big deal out of this

ALTER DATABASE Sample2016 SET RECOVERY SIMPLE

--Netflix and chill

USE [Sample2016];

--Make sure this stuff is gone

IF OBJECT_ID('dbo.Rockwell') IS NOT NULL

BEGIN

ALTER TABLE [dbo].[Rockwell] SET (SYSTEM_VERSIONING = OFF);

DROP TABLE [dbo].[Rockwell]

DROP TABLE [dbo].[RockwellHistory]

END

IF (SELECT [ps].[name] FROM [sys].[partition_schemes] AS [ps] WHERE [ps].[name] = 'TemporalDailyScheme') IS NOT NULL

BEGIN

DROP PARTITION SCHEME [TemporalDailyScheme];

END

IF (SELECT [pf].[name] FROM [sys].[partition_functions] AS [pf] WHERE [pf].[name] = 'TemporalDailyPFunc') IS NOT NULL

BEGIN

DROP PARTITION FUNCTION [TemporalDailyPFunc];

END

/*I always feel like...*/

/*Somebody's watching me...*/

CREATE TABLE [dbo].[Rockwell]

(

[ID] BIGINT,

[ProductName] NVARCHAR(50) NOT NULL,

[Price] DECIMAL(18,2) NOT NULL,

[Description] VARCHAR(8000) NOT NULL,

[AuditDateStart] DATETIME2(2) GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,

[AuditDateEnd] DATETIME2(2) GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,

PERIOD FOR SYSTEM_TIME ([AuditDateStart], [AuditDateEnd]),

CONSTRAINT [pk_id] PRIMARY KEY CLUSTERED ([ID])

) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[RockwellHistory]) )

/*Partitioning Crap*/

--Make a partitioning function

DECLARE @sql NVARCHAR(MAX) = N'', @i INT = -366, @lf NCHAR(4) = NCHAR(13) + NCHAR(10)

SET @sql = N'CREATE PARTITION FUNCTION TemporalDailyPFunc (DATETIME2(2)) AS RANGE RIGHT FOR VALUES ('

WHILE @i < 0

BEGIN

SET @sql += @lf + 'DATEADD(DAY, ' + CAST(@i AS NVARCHAR) + ', ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) + N' )' + N', '

SET @i += 1

END

SET @sql += @lf + 'DATEADD(DAY, ' + CAST(@i AS NVARCHAR) + ', ' + CAST(CAST(GETDATE() AS DATE) AS NVARCHAR) + N' )' + N');';

EXEC sp_executesql @sql;

--PRINT @sql

--I don't have a ton of filegroups, I just want everything on Primary

CREATE PARTITION SCHEME TemporalDailyScheme

AS PARTITION TemporalDailyPFunc

ALL TO ( [PRIMARY] );

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:

DROP INDEX [ix_RockwellHistory] ON dbo.RockwellHistory

1	DROP INDEX [ix_RockwellHistory] ON dbo.RockwellHistory

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

CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

1	CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

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!

CREATE NONCLUSTERED INDEX [ix_RockwellHistory_ADS] ON [dbo].[RockwellHistory] ([AuditDateStart]) ON TemporalDailyScheme([AuditDateStart]);

1	CREATE NONCLUSTERED INDEX [ix_RockwellHistory_ADS] ON [dbo].[RockwellHistory] ([AuditDateStart]) ON TemporalDailyScheme([AuditDateStart]);

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.

CREATE CLUSTERED INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory] ([AuditDateStart]) ON TemporalDailyScheme([AuditDateStart]);

CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

CREATE CLUSTERED INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory] ([AuditDateStart]) ON TemporalDailyScheme([AuditDateStart]);

CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

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.

CREATE CLUSTERED COLUMNSTORE INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory] WITH (DROP_EXISTING = ON) ON TemporalDailyScheme([AuditDateStart]);

1	CREATE CLUSTERED COLUMNSTORE INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory] WITH (DROP_EXISTING = ON) ON TemporalDailyScheme([AuditDateStart]);

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.

DROP INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory]

CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

DROP INDEX [cx_RockwellHistory_ADS] ON [dbo].[RockwellHistory]

CREATE CLUSTERED COLUMNSTORE INDEX [cx_cs_RockwellHistory] ON [dbo].[RockwellHistory] ON TemporalDailyScheme ([AuditDateStart]);

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.

SQL Interview Question: “Tell me what you see in this screenshot.”

Spring Cleaning Your Databases

7 Comments. Leave new

sqltomato
May 25, 2016 2:08 pm

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
Debra
January 24, 2017 10:36 am

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
Erik Darling
January 24, 2017 11:33 am

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

Reply
Debra
January 25, 2017 3:32 am

Thanks I shall do 🙂

Reply
Samuel Jones
March 28, 2017 10:18 am

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
- Erik Darling
  March 28, 2017 1:01 pm
  
  Oh, cool! I’ll have to give that a shot!
  
  Reply
Marce
July 31, 2017 11:26 pm

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