System Versioned Tables In SQL Server

Feature introduced from SQL Server 2016

One of the easiest option to configure data audit is using system versioned table. Lets taken a scenario if you have a critical table to do an audit on transactions you might have to write triggers or events, but now you can use Versioned table to setup audit easier.

Lets begin with example to create versioned table

Conditions while creating new versioned table

• Table must have primary Key
• Declare two datetime2 type columns
• Specify PERIOD FOR SYSTEM_TIME
• If you not mentioned any table name for history, sql server itself generate new table.

Below is the example to specify the history table details along with main table creation.

Versioned table under database

Now lets begin to insert data in to invoice table and then understand the usage of versioned table.

Now let change the amount value on existing records and see the changes on history table.

Easily we can track any update/Delete with this versioned table option. But there are some limitations while using this option

• Replication would not support for any Temporal tables