In SQL Server 2017, you can create temporal tables from scratch, but you can also alter an existing table and add attributes to it to convert it to a system-versioned temporal table. All you need to do is to add period columns, define the SYSTEM_TIME period on them, and set the temporal attribute. In this section, for example, you will convert the Department table from the AdventureWorks2017 database into a temporal table. The following screenshot shows the table's content:

You can see that the table has one column representing temporality. The ModifiedDate column shows the time a table row became valid, that is, when it was recently updated. However, since there is no history table, you don't know previous row versions. By converting the table to a temporal table, you can retain the existing functionality, and add the temporal one. The following code example demonstrates how to convert the Department table in the AdventureWorks2017 database to a temporal table:

USE AdventureWorks2017; 
--the ModifiedDate column will be replaced by temporal table functionality
ALTER TABLE HumanResources.Department DROP CONSTRAINT DF_Department_ModifiedDate;
ALTER TABLE HumanResources.Department DROP COLUMN ModifiedDate;
GO
ALTER TABLE HumanResources.Department 
ADD ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL CONSTRAINT DF_Validfrom DEFAULT '20080430 00:00:00.0000000', 
   ValidTo DATETIME2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL CONSTRAINT DF_ValidTo DEFAULT '99991231 23:59:59.9999999', 
   PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); 
GO 
ALTER TABLE HumanResources.Department SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = HumanResources.DepartmentHistory)); 

Since the existing temporal data implementation will be replaced by an SQL Server 2017 temporal table, the ModifiedDate column is not necessary anymore and can be removed. Furthermore, you have to use two ALTER statements; with the first statement, you define the period columns and the period, while the second statement sets the SYSTEM_VERSIONING attribute to ON and defines the name of the history table that will be created by the system.

You should be aware that you need to provide default constraints for both columns, since they must be non-nullable. You can even use a date value from the past for the default constraint of the first period column (as shown in the preceding code example, where the value is set to the one from the removed column); however, you cannot set values in the future. Finally, period date columns are defined with the HIDDEN attribute to ensure that there will be no breaking changes in the existing code.

Any further change in the table will be automatically handled by the system and written in the HumanResources.DepartmentHistory history table. For instance, you can update the Name attribute for the department with the ID of 2 and then check the values in both tables:

UPDATE HumanResources.Department SET Name='Political Correctness' WHERE DepartmentID = 2;
SELECT * FROM HumanResources.Department WHERE DepartmentID = 2;
SELECT * FROM HumanResources.DepartmentHistory;

The first table simply shows the new value, while the second shows the previous one, with appropriate validity dates from a system point of view (the time part of the date is shortened for brevity):

DepartmentID  Name                   GroupName
------------ ----------------------  ---------------------------
2            Political Correctness   Research and Development


DepartmentID Name       GroupName                 ValidFrom   ValidTo
------------ --------   ------------------------ ----------  ---------  
2           Tool Design Research and Development  2008-04-30 2017-12-15 

As you can see, it is very easy and straightforward to add temporal functionality to an existing non-temporal table. It works transparently; all your queries and commands will work without changes.