Temporal Tables and Table Partitioning In SQL Server: Not If You Like Switching Partitions

These Are A Few Of Not My Favorite Things

---

This is the part of SQL Server I often find myself rolling my eyes at: poor feature interoperability, and that V1 smell that… never seems to turn into that V2 smell.

The full script is hosted here. I don’t want a tedious post full of setting up partitioning, etc.

I wanna get to the stuff that you might care about later.

If You Wanna…

---

The utility of Partitioning is being able to quickly switch partitions in and out. Data management. Not query performance (unless you’re using columnstore).

If you want to do that with temporal tables, your staging and “out” tables need to match exactly, down to the temporal-ness.

For example, this won’t work:

CREATE TABLE dbo.Votes_Deletes_Stage
(
    Id INT NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    SysStartTime DATETIME2(7) NOT NULL, --Versioning column
    SysEndTime DATETIME2(7) NOT NULL --Versioning column
    CONSTRAINT dr_rockzo_Stage PRIMARY KEY CLUSTERED (CreationDate, Id) ON [PRIMARY]
) ON [PRIMARY];

You gotta have all the same stuff you used to get your partitioned table set up for temporal-ness.

CREATE TABLE dbo.Votes_Deletes_Stage
(
    Id INT NOT NULL,
    PostId INT NOT NULL,
    UserId INT NULL,
    BountyAmount INT NULL,
    VoteTypeId INT NOT NULL,
    CreationDate DATETIME NOT NULL,
    SysStartTime DATETIME2(7) GENERATED ALWAYS AS ROW START HIDDEN
        CONSTRAINT df_VotesDeletes_Stage_SysStartTime
            DEFAULT SYSDATETIME(),
    SysEndTime DATETIME2(7) GENERATED ALWAYS AS ROW END HIDDEN
        CONSTRAINT df_VotesDeletes_Stage_SysEndTime
            DEFAULT CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')
    CONSTRAINT dr_rockzo_Stage PRIMARY KEY CLUSTERED (CreationDate, Id) ON [PRIMARY],
  PERIOD FOR SYSTEM_TIME([SysStartTime], [SysEndTime])
) ON [PRIMARY];

Then If You Wanna…

---

Switch data in or out, you have to turn off the temporal-ness.

Msg 13546, Level 16, State 1, Line 97
Switching out partition failed on table ‘DeletesDemo.dbo.Votes_Deletes’ because it is not a supported operation on system-versioned tables. Consider setting SYSTEM_VERSIONING to OFF and trying again.

“Consider turning off the feature that takes forever to turn back on with large tables so you can do the thing partitioning does quickly”

Don’t worry, the color red you’re seeing is totally natural.

And hey, once you’ve turned it off, you can swap a partition in or out.

A Normal Partitioning Problem

---

The partition you’re going to switch in needs to have a constraint on it that tells the whatever that the data you’re switching in is valid for the partition you’re swapping it into.

Msg 4982, Level 16, State 1, Line 105
ALTER TABLE SWITCH statement failed. Check constraints of source table ‘DeletesDemo.dbo.Votes_Deletes_Stage’ allow values that are not allowed by range defined by partition 8 on target table ‘DeletesDemo.dbo.Votes_Deletes’.

The thing is, this error message sucks. It sucks all the suck. Especially when dealing with temporal tables, you might think something odd happened with the constraints on your versioning columns. They both have constraints on them. WHICH CONSTRAINT IS THE PROBLEM?

If you’re new to Partitioning, you may not have ever switched data into or out of a table before. This error message can be a real head-scratcher.

The fix is to add a check constraint to your staging table — the one you’re swapping in — that tells Partitioning about what’s in the table. In my case, I have the Votes_Deletes table partitioned by CreationDate, by one year ranges. For me, Partition 8 contains values for the year 2013. To make sure it’s safe to swap my staging partition into the partition for that year, it needs a constraint:

ALTER TABLE dbo.Votes_Deletes_Stage
ADD CONSTRAINT ck_yrself 
    CHECK (CreationDate >= '20130101' AND CreationDate < '20140101' 
         AND CreationDate IS NOT NULL);

And You Should Probably

---

Turn the temporal-ness back on. When you do that, you have an option. Do you want to make sure your data is consistent?

ALTER TABLE dbo.Votes_Deletes SET (SYSTEM_VERSIONING = ON
 ( HISTORY_TABLE=dbo.Votes_Deletes_History,
   DATA_CONSISTENCY_CHECK= ON) );

If you don’t, re-enabling is instant. Buuuuuuut you take the chance that some data in your table may have changed while you were tooting around trying to get partitions swapped in and out. I have no idea what the ramifications of skipping the check might be. In the context of this post, probably nothing. If you’ve got a full history table and the specter of changes during this whole shebang…

This is what the query plan for turning it back on looks like.

There’s nothing in the history table. If there were, this could get really bad (trust me, ha ha ha). What checks do we do when the history table is empty?

Le Shrug, as they say in the land of protest.

But a 53 million row assert sure does take a couple minutes.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.