Hello Friends,

In my previous blog post, we have seen two different ways for the creation of a temporal history table. First was the default, where SQL Server will create the table and assign a default name to that table. While the second was specify the name of that table and SQL Server will create the history table with that name. In both these cases structure of the history table will be same and history table will have one clustered row store index. In today’s world where we have the support for column store indexes, because of the workload pattern, you can also create clustered column store index.

Yes, you read it correctly. You can create a temporal history table with a clustered column store index. Afterwards you can specify this table as the temporal history table while creating temporal table. Let me show you how to do the same:

After executing the above TSQL code, I got the below mentioned error, because I have specified LifeStart and LifeEnd both the columns as NULL. These columns will be used internally by SQL Server to store the valid period information.

Msg 13530, Level 16, State 1, Line 22

Setting SYSTEM_VERSIONING to ON failed because system column ‘LifeStart’ in history table ‘TEMPORAL.dbo.TemporalTableDemo3_History’ corresponds to a period column in table ‘TEMPORAL.dbo.TemporalTableDemo3’ and cannot be nullable.

Let me try to modify the temporal history table and then I’ll create the temporal table again:

All ran fine. We can check the details in Object Explorer:

SQL Server 2016 - Temporal Tables Part 2 -1

In SSMS 2016, Temporal history table is shown under the temporal table, but If you will see the above information on SQL Server 2012 management studio the it will look like two different tables and clustered column store index will be shown as Non Clustered Index:

SQL Server 2016 - Temporal Tables Part 2 -2

In the next blog post we will look into some more details about temporal tables.

HAPPY LEARNING!

Regards:

Prince Kumar Rastogi

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook

Follow Prince Rastogi on Twitter | Follow Prince Rastogi on FaceBook