SQL Server System versioned temporal table

There are times when we need to maintain an audit trail of data changes made to a database table. SQL Server 2016 added support for Temporal tables to achieve this.

Adding versioning to an existing table
A Non-Temporal Table can be changed into a System-Versioned Temporal Table by adding PERIOD definitions and enabling System versioning as below.

ALTER TABLE myschema.mytable
    ADD   
        SysStartTime DATETIME2(0) GENERATED ALWAYS AS ROW START HIDDEN
            CONSTRAINT DF_SysStart DEFAULT SYSUTCDATETIME()
      , SysEndTime DATETIME2(0) GENERATED ALWAYS AS ROW END HIDDEN
            CONSTRAINT DF_SysEnd DEFAULT CONVERT(DATETIME2 (0), '9999-12-31 23:59:59'),
        PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime);        

ALTER TABLE myschema.mytable  
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = history.trial_mytable));

Querying historical data from a temporal table
The power of this feature lies in its simplicity. Once System versioning is enabled, we can forget about the history table. SQL Server manages the audit/history of changes for us. We could directly query the main table and get a snapshot of how the data looked at any point in the past using the syntax below.

select *
from myschema.mytable
FOR SYSTEM_TIME AS OF '2020-12-31 17:47:46' ;

Another good thing is that only the changes get stored in the history table. That is, if we created a table, enabled versioning and just inserted a bunch of rows to it, then the history table will be empty. Only if the data in the main table is updated or deleted, the changes get into the history table. In other words, SQL Server does not create duplicate records between the main table and history table.

Refer to SQL Server documentation for some of the limitations / things to keep in mind when using System-versioned temporal tables.

Source :
https://docs.microsoft.com/en-us/sql/relational-databases/tables/creating-a-system-versioned-temporal-table?view=sql-server-2017