Search code examples
sql-server-2016temporal-tables

How do I create a temporal table in SQL Server 2016?

I want to know how to create a system-versioned table, when I want to create a new temporal table in SQL Server 2016?

Solution

  • From here:

    In order to make a table a system-versioned table it needs the following:

    • A primary key
    • The table option SYSTEM_VERSIONING to be set to ON
    • Two non-nullable DATETIME2() columns representing the start and end of the row’s validity period
      • The start column should be marked with the option GENERATED ALWAYS AS ROW START
      • The end column should be marked with the option GENERATED ALWAYS AS ROW END
    • Designation of the period columns: PERIOD FOR SYSTEM_TIME (, )
    • A linked history table (which SQL Server can create for you) to hold the past states of modified rows