In my last article (https://www.databasejournal.com/ms-sql/altering-an-existing-table-to-support-temporal-data/) I showed you how to modify an existing table to take advantage of the new temporal data feature in SQL Server 2016. By having a temporal data table (history table) I was able to show the changes that have taken place after temporal data support for an existing table was enabled. In this article I plan to expand the topic to show you how to create and deleted system-versioned tables and the associated history temporal table.
Business Situation
Now that SQL Server 2016 has finally been released management is keen on using this feature as part of a new Employee Management portal application we are building. One of the first tables they want to create that will track historical data is a table named dbo.EmpSalary. This table will contain the salaries of current employees, and the historical records associated with this table. By using the historical temporal data support in SQL Server 2016, management will be able to track salary changes over time. By creating this system-versioned table using the new temporal data table the new Employee Management portal application will be able to track employees’ salaries over time.
Creating a System-Generated History Table
A system-generated history table is a table that is automatically named by SQL Server when it is created. The following CREATE TABLE script will create a history table with a system generated name:
CREATE TABLE EmpSalary ( EmpID int NOT NULL PRIMARY KEY CLUSTERED , SalaryAmt decimal (10,2) NULL , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT) ) WITH (SYSTEM_VERSIONING = ON);
Here I created a table named EmpSalary. Note that I didn’t specify the name of the history table. All I specified was the “WITH (SYSTEM_VERSIONING = ON)” specification. By using the WITH clause I only identified that I wanted a system generated history table.
If we look at the Object Explorer details in SQL Server Management Studio (SSMS), after creating this table we can see the system generated temporal data history table. Here is an image of my Object Explorer output:
Object Explorer Output
As you can see my table dbo.EmpSalary is identified as a “System-Versioned” table. This tells me that there is a history table associated with this table. The second line of output in the above screenshot shows the actual history table that was created. For this example that table name is dbo.MSSQL_TemporalHistoryFor_565577053. That table name is a mouthful, but what can you expect for a system generated table name.
Dropping a System-Versioned Table and the Associated Historical Table
I personally don’t like system generated names. As you can see in the prior example above, the historical temporal data table created by the system is not the easiest to remember. Therefore instead of using the system-generated table name as in the above example, I will drop my system generated history table and the associated temporal table.
If you are like me you might be thinking that you can delete a system-generated history table and its associated table by right-clicking on the system-versioned table name, in Object Explorer and then just right clicking the “Delete” menu item. If you think that you’d be wrong. Here is what I see when I do a right-click on my dbo.EmpSalary table:
Right-click on dbo.EmpSalary Table
As you can see from the menu above there is no delete option displayed when I clicked on my dbo.EmpSalary table. So how do you delete a system-versioned table?
It is not a single statement process to delete a system-system versioned. To generate the statements necessary to delete my dbo.EmpSalary table I right clicked on the table selected the “DROP to” scripting option to the clipboard. You can see how I selected to generate a drop script below:
How to Select to Generate a Drop Script
After selecting the “DROP to” option the following script was placed in my clipboard:
USE [TemporalDemo] GO /****** Object: Table [dbo].[EmpSalary] Script Date: 6/20/2016 6:18:21 AM ******/ ALTER TABLE [dbo].[EmpSalary] SET ( SYSTEM_VERSIONING = OFF ) GO /****** Object: Table [dbo].[EmpSalary] Script Date: 6/20/2016 6:18:21 AM ******/ DROP TABLE [dbo].[EmpSalary] GO /****** Object: Table [dbo].[MSSQL_TemporalHistoryFor_565577053] Script Date: 6/20/2016 6:18:21 AM ******/ DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_565577053] GO
Here you can see that first I have to ALTER the table dbo.EmpSalary to set the system versioning to off. As soon as I perform that ALTER statement my dbo.EmpSalary table is no longer versioned, but the history table dbo.MSSQL_TemporalHistoryFor_665577053 becomes real table. This can be seen in the following SQL Server Object Explorer window:
SQL Server Object Explorer
To finish up dropping my original dbo.EmpSalary table and the system generated history table I run the following commands from a SQL Server Query window:
/****** Object: Table [dbo].[EmpSalary] Script Date: 6/20/2016 6:18:21 AM ******/ DROP TABLE [dbo].[EmpSalary] GO /****** Object: Table [dbo].[MSSQL_TemporalHistoryFor_565577053] Script Date: 6/20/2016 6:18:21 AM ******/ DROP TABLE [dbo].[MSSQL_TemporalHistoryFor_565577053] GO
As you can see it takes a few more steps to drop a system versioned table and its associated history table.
Creating a Named Historical Temporal table
Like I said earlier I don’t like default names for SQL Server objects. Therefore I want to show you how to create a temporal table that has a name that I will define. There are two different ways to create a temporal table that is named. The first method I will show you is to let SQL Server generate the column definitions based on the base table. Below is some code to create a temporal table that I’ve named, but lets the system generate the column definitions for the historical temporal table from the definition of the based table:
CREATE TABLE dbo.EmpSalary ( EmpID int NOT NULL PRIMARY KEY CLUSTERED , SalaryAmt decimal (10,2) NULL , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpSalaryHistory));
Here you can see that I created my temporal history table with a name by using the HISTORY_TABLE clause on the CREATE TABLE statement. Note that when specifying the history table name it needs to be a two part name. In this example I didn’t specify any column definitions for the history table. The columns defined in the history table are the same as my dbo.EmpSalary.
Creating a History Table from an Existing Table
In all the examples I’ve shown you so far, all the history tables have been generated for me by SQL Server. You can also create a history table from an existing table, as I have done in the following example:
USE [TemporalDemo] GO CREATE TABLE [dbo].[EmpSalaryHistory]( [EmpID] [int] NOT NULL, [SalaryAmt] [decimal](10, 2) NULL, [SalaryBeginDT] [datetime2](7) NOT NULL, [SalaryEndDT] [datetime2](7) NOT NULL ) ON [PRIMARY] CREATE CLUSTERED INDEX [ix_EmpSalaryHistory] ON [dbo].[EmpSalaryHistory] ( [SalaryEndDT] ASC, [SalaryBeginDT] ASC ); CREATE TABLE dbo.EmpSalary ( EmpID int NOT NULL PRIMARY KEY CLUSTERED , SalaryAmt decimal (10,2) NULL , SalaryBeginDT datetime2 GENERATED ALWAYS AS ROW START NOT NULL , SalaryEndDT datetime2 GENERATED ALWAYS AS ROW END NOT NULL , PERIOD FOR SYSTEM_TIME (SalaryBeginDT, SalaryEndDT) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmpSalaryHistory)); GO
Here you can see that I first created my history table, dbo.SalaryHistory then I went on to create my dbo.EmpSalary table, which is system-versioned and attached to the history table I created. By doing this I was allowed to define a clustered-index on the history table. Note you can also add indexes to the history table after the table exists. This flavor of creating a history table doesn’t really allow you to do anything different than you would do any other way. It just allows you another option for creating a history table. If your application already has a history table then possibly this option might allow you to attach that history table to the table that contains current records.
Summary
Creating a temporal data history table while you create your table is easy by just adding the “SYTEM_VERSIONING’ clause to your create table statement. Keep in mind you can either let SQL Server generate the history table name, or you can specify a history table name with your “CREATE TABLE” statement. Next time you have a business need to track table records changing over time, don’t write application code to do this, but instead build a history table, using the temporal data table feature of SQL Server 2016.
