I’ve been testing the new Temporal Tables feature over the past day to see about using it in one of our production databases. It’s a neat feature that honestly adds a boat load of possibility around logging.

In my testing I noticed that user created tables seem to store the rows over quite a bit more pages. User created history tables were nearly double the size of an auto generated one. If you’re currently using the feature or plan to use it in the near future, you’ll want to think about this storage issue before you implement.

If you’re not aware of what Temporal tables are, they are like a running history of your table. You can read more about them here:

https://msdn.microsoft.com/en-us/library/dn935015.aspx

User created vs System generated history tables?

With Temporal Tables, you can create you history table in one of three ways:

1. Create the table yourself ahead of time and then reference it.
2. Don’t specify a history table at all and SQL 2016 will create a new table with a cryptic system generated name. – ick
3. Specify a name and SQL 2016 will create the table for you.

Each of these are done with the ALTER TABLE statement.

ALTER TABLE dbo.Address
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.AddressHistory))

OR

ALTER TABLE dbo.Address
SET (SYSTEM_VERSIONING = ON)

The second example creates a table with a system generated name.

There’s something else you should know

At first I was a bit puzzled. I noticed that the system generated table was consistently smaller than my user created table. It was not only smaller it was twice as small!

I did some further testing on my Surface this weekend and here’s what I found:

— Side note:  I use Person.Address a lot in demos, so I decided to create a new table to test with in hopes of not breaking any other demos I do regularly.

The script

use AdventureWorks2012
go

–CREATE THE TABLE
SELECT * INTO dbo.Address FROM Person.Address

–ADD REQUIRED COLUMNS
ALTER TABLE dbo.Address

ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN,

ValidTill datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTill)
GO

CREATE SCHEMA History
GO

CREATE TABLE History.AddressHistory

(    AddressID int NOT NULL,

AddressLine1 nvarchar(60) NOT NULL,

AddressLine2 nvarchar(60) NULL,

City nvarchar(30) NOT NULL,

StateProvinceID int NOT NULL,

PostalCode nvarchar(15) NOT NULL,

SpatialLocation geography NULL,

rowguid uniqueidentifier NOT NULL,

ModifiedDate datetime NOT NULL,

ValidFrom datetime2(0) NOT NULL,

ValidTill datetime2(0) NOT NULL)
GO

ALTER TABLE dbo.Address
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.AddressHistory))
GO

UPDATE dbo.Address SET AddressLine2 = ‘ABC’

GO
— RUN TOP TABLES REPORT

— History table has 3 MB of data

UPDATE dbo.Address SET AddressLine2 = ‘ABC’
–Note that no data actually changes but the update is executed
GO

— RUN TOP TABLES REPORT AGAIN

— History table has 6 MB of data

UPDATE dbo.Address SET AddressLine2 = ‘ABC’
GO 3

— RUN TOP TABLES REPORT AGAIN

— History table has 15 MB of data

— NOW LET’S LOOK AT A SYSTEM GENERATED TABLE

ALTER TABLE dbo.address
SET (SYSTEM_VERSIONING = OFF)
go

ALTER TABLE dbo.Address
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = History.AddressHistoryNEW))

GO

UPDATE dbo.Address SET AddressLine2 = ‘ABC’

GO

— RUN TOP TABLES REPORT AGAIN

— History table has 1.8 MB of data?

UPDATE dbo.Address SET AddressLine2 = ‘ABC’

GO

— RUN TOP TABLES REPORT AGAIN

— History table has 3.6 MB of data?

UPDATE dbo.Address SET AddressLine2 = ‘ABC’

GO 3

As you can see the system generated table is nearly half the size of the user created table. I scripted the tables to compare and they were the same! I then looked at Object Explorer and noted that the system generated table actually had a bit more (clustered PK and some defaults).

Why is the table SQL generated so much smaller?

Towards the end of the weekend I decided to pick this up again and I looked at sys.partitions. Here’s what I found:

That’s right! When SQL generates your table for you it adds a clustered index and also applies page compression.

Summing up – the takeaways

To make things very easy and ensure the table is as optimal as it can be, I recommend letting SQL Server 2016 create your history table. Give it a meaningful name; but, let SQL created it for you.

This will ensure that page compression is enabled on the history table and will create an index that may be useful as well.

I hope you’ve found this post helpful! If you did be sure you check out my other posts:

http://www.sqlservercentral.com/blogs/confessions-of-a-microsoft-addict/

http://www.sqltechblog.com

Also find me on LinkedIn and Twitter!