Most probably, you have had to deal with historical data in the past. Since there was no out-of-the-box feature in previous SQL Server versions, you had to create a custom temporal data solution. Now that the feature is available, you might think to use it for your existing temporal solutions. You saw earlier in this chapter that you can define your own history table. Therefore, you can also use an existing and populated historical table. If you want to convert your existing solution to use system-versioned temporal tables in SQL Server 2017, you have to prepare both tables so that they fill all requirements for temporal tables. To achieve this, you will again use the AdventureWorks2017 database and create both current and history tables by using tables that exist in this database. Use the following code to create and populate the tables:

USE WideWorldImporters; 
CREATE TABLE dbo.ProductListPrice 
( 
   ProductID INT NOT NULL CONSTRAINT PK_ProductListPrice PRIMARY KEY, 
   ListPrice MONEY NOT NULL, 
); 
INSERT INTO dbo.ProductListPrice(ProductID,ListPrice) 
SELECT ProductID,ListPrice FROM AdventureWorks2017.Production.Product; 
GO 
CREATE TABLE dbo.ProductListPriceHistory 
( 
   ProductID INT NOT NULL, 
   ListPrice MONEY NOT NULL, 
   StartDate DATETIME NOT NULL, 
   EndDate DATETIME   NULL, 
   CONSTRAINT PK_ProductListPriceHistory PRIMARY KEY CLUSTERED  
   ( 
         ProductID ASC, 
         StartDate ASC 
   ) 
); 
INSERT INTO dbo.ProductListPriceHistory(ProductID,ListPrice,StartDate,EndDate) 
SELECT ProductID, ListPrice, StartDate, EndDate FROM AdventureWorks2017.Production.ProductListPriceHistory;  

Consider the rows for the product with an ID of 707 in both tables:

SELECT * FROM dbo.ProductListPrice WHERE ProductID = 707; 
SELECT * FROM dbo.ProductListPriceHistory WHERE ProductID = 707; 

Here are the rows in the current and history tables respectively:

ProductID   ListPrice
---------   ---------
707         34.99
    
ProductID  ListPrice   StartDate               EndDate
---------  ---------   -------------------     -------------------
707        33.6442     2011-05-31 00:00:00.000  2012-05-29 00:00:00.000
707        33.6442     2012-05-30 00:00:00.000  2013-05-29 00:00:00.000
707        34.99       2013-05-30 00:00:00.000  NULL    

Assume that this data has been produced by your temporal data solution and that you want to use system-versioned temporal tables in SQL Server 2017 instead of it, but also use the same tables. The first thing you have to do is align the columns in both tables. Since the current table has no date columns, you need to add two period columns and define the period. The columns should have the same name as the counterpart columns from the history table. Here is the code that creates the temporal infrastructure in the current table:

ALTER TABLE dbo.ProductListPrice 
ADD StartDate DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_StartDate1 DEFAULT '20170101 00:00:00.0000000', 
   EndDate DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_EndDate1 DEFAULT '99991231 23:59:59.9999999', 
   PERIOD FOR SYSTEM_TIME (StartDate, EndDate); 
GO 

The next steps are related to the history table. As you can see from the sample data, your current solution allows gaps in the history table and also contains the current value with the undefined end date. As mentioned earlier in this chapter, the history table only contains historical data and there are no gaps between historical entries (the new start date is equal to the previous end date). Here are the steps you have to implement in order to prepare the dbo.ProductLisPriceHistory table to act as a history table in a system-versioned temporal table in SQL Server 2017:

• Update the non-nullable EndDate column to remove the gap between historical values described earlier and to support the open-closed interval
• Update all rows where the EndDate column is null to the StartDate of the rows in the current table
• Remove the primary key constraint
• Change the data type for both date columns StartDate and EndDate to DATETIME2

Here is the code that implements all these requests:

--remove gaps 
UPDATE dbo.ProductListPriceHistory SET EndDate = DATEADD(day,1,EndDate); 
--update EndDate to StartDate of the actual record 
UPDATE dbo.ProductListPriceHistory SET EndDate = (SELECT MAX(StartDate) FROM dbo.ProductListPrice) WHERE EndDate IS NULL; 
--remove constraints 
ALTER TABLE dbo.ProductListPriceHistory DROP CONSTRAINT PK_ProductListPriceHistory; 
--change data type to DATETIME2 
ALTER TABLE dbo.ProductListPriceHistory ALTER COLUMN StartDate DATETIME2 NOT NULL; 
ALTER TABLE dbo.ProductListPriceHistory ALTER COLUMN EndDate DATETIME2 NOT NULL; 

Now, both tables are ready to act as a system-versioned temporal table in SQL Server 2017:

ALTER TABLE dbo.ProductListPrice SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductListPriceHistory,  DATA_CONSISTENCY_CHECK = ON)); 

The command has been executed successfully and the dbo.ProductListPriceHistory table is now a system-versioned temporal table. Note that the DATA_CONSISTENCY_CHECK = ON option is used to check that all rows in the history table are valid from a temporal data point of view (no gaps and the end date is not before the start date). Now, you can check the new functionality by using the UPDATE statement. You will update the price for the product by changing the ID of 707 to 50 and then check the rows in both tables:

UPDATE dbo.ProductListPrice SET ListPrice = 50 WHERE ProductID = 707; 
SELECT * FROM dbo.ProductListPrice WHERE ProductID = 707; 
SELECT * FROM dbo.ProductListPriceHistory WHERE ProductID = 707; 

Here are the rows for this product in both tables: 

ProductID   ListPrice
---------   ---------
707         50.00
    
ProductID ListPrice  StartDate                    EndDate
------- -------- -------------------------- ------------------------
707     33.6442 2011-05-31 00:00:00.0000000 2012-05-30 00:00:00.0000000
707     33.6442 2012-05-30 00:00:00.0000000 2013-05-30 00:00:00.0000000
707     34.99   2013-05-30 00:00:00.0000000 2017-12-12 21:21:08.4382496
707     34.99   2017-12-12 21:21:08.4382496 2017-12-12 21:21:08.4382496

You can see another row in the history table (compare with the previous result). Of course, when you try these examples, you will get different values for the columns StartDate and EndDate, since they are managed by the system.

As you can see, it is not so complicated to migrate an existing solution to a system-versioned table in SQL Server 2017, but it is not a single step. You should take into account that most probably it will take time to update the data type to DATETIME2. However, by using the system-versioned temporal tables feature, your history tables are completely and automatically protected from changes by anyone except the system. This is a great out-of-the-box data consistency improvement.