Hello Friends,

In my previous blog posts about temporal tables, we have seen three different ways for the creation of a temporal history table. First was the default where SQL Server create the table and assign a default name to that table. Second was where we specify the name of that table and SQL Server will create the history table with that name. In both of the cases, structure of the history table was same and table was having one clustered row store index. In the third, we have seen the support for column store indexe. You can click here for Part1 and Part2.

Today we will take a look about a scenario, where we want to covert the existing non temporal table to the system version table. Here existing table is already having data into it.

For the demo purpose, I am using an AdventureWorks sample database for SQL Server 2016 CTP 3 on SQL Server 2016. I’ll create the demo table almost similar to [Sales].[CreditCard]. Then I’ll populate this table with the data from [Sales].[CreditCard].

Now, I’ll try to convert this Test.CreditCard non temporal table to system versioned temporal table. I’ll add two columns of datetime2 data type with default values and also specify PERIOD FOR SYSTEM_TIME on both of these columns. Finally, I’ll do the system versioning ON. In our case, we are creating a new history table, so we can turn the data consistency check to off.

From Object Explorer, you can check that table has been converted to system versioned temporal table.

SQL Server 2016 - Temporal Tables

In the next blog post we will look about querying the temporal tables.


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