How to hide SysStartTime and SysEndEtime columns in Temporal Tables

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;