Temporal table was introduced with SQL Server 2016 and it is designed to capture and store changes of data in tables. In other words, similar to Change Data Capture (CDC), Change Tracking (CT), Temporal table maintains the history with changed details.
Temporal table needs two additional columns called SysStartTime and SysEndTime. Once they are added, they can be seen with the table just like other columns and will be appeared with SELECT * statement. Although it is not recommended to write SELECT * type of query against tables, unfortunately it can still be seen with many application and the database I had to analyze today had similar codes in almost all areas in the application. I had to make two tables as Temporal Tables and I had to make sure that it does not break the existing application.
Fortunately, SQL Server has given a solution for handling it. I was able to alter the table and make it as a Temporal Table without making changes to any statement written in the application while making sure that SELECT * does not return newly added SysStartTime and SysEndTime columns.
If you use, HIDDEN keyword when creating the Temporal Table, it makes sure that these two columns are not appeared when SELECT * is performed. However, columns can be explicitly mentioned in the SELECT if required.
-- changing existing table by adding columns ALTER TABLE dbo.Customer ADD ValidFrom datetime2(0) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_SysStartTime DEFAULT CONVERT(datetime2 (0), '2017-02-24 00:00:00') , ValidTo datetime2(0) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DF_SysEndTime DEFAULT CONVERT(datetime2 (0), '9999-12-31 23:59:59') , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo); GO -- turning versioning on ALTER TABLE dbo.Customer SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CustomerHistory)); GO -- Checking records SELECT * FROM dbo.Customer; SELECT *, ValidFrom, ValidTo FROM dbo.Customer; SELECT * FROM dbo.CustomerHistory;