What is a temporal table in SQL Server?

What is a temporal table in SQL Server?

What is Temporal Table? Temporal tables, also known as system-versioned tables, provide us with new functionality to track data changes. It allows SQL Server to maintain and manage the history of the data in the table automatically. This feature provides a full history of every change made to the data.

How to manage data in the temporal history table?

The following four approaches for managing historical data in the temporal history table are available: With each of these approaches, the logic for migrating or cleaning history data is based on the column that corresponds to end of period in the current table.

How to stretch the temporal table in SQL Server?

In SQL Server 2016 (13.x), you cannot right-click the temporal history table itself and click Stretch. Right-click your database and point to Tasks, point to Stretch, and then click Enable to launch the wizard.

Why does the history table increase database size?

With system-versioned temporal tables, the history table may increase database size more than regular tables, particularly under the following conditions: You retain historical data for a long period of time. You have an update or delete heavy data modification pattern.

Why do I need two columns in a temporal table?

datetime2 columns. Every temporal table must have two columns with a datatime2 data type. These columns are used exclusively by the system to record the period of validity for each row whenever a row is changed. Both columns should be defined as NOT .

Do you need a primary key for a temporal table?

Consider the following when working with temporal tables: A temporal table must have a primary key defined in order to correlate records between the current table and the history table, and the history table cannot have a primary key defined.

How are period columns defined in SQL Server?

The two period columns which are of datetime2 data type, SysStartTime and SysEndTime define the current and historical data for every row of the table. It defines the validity of the data. The DateTime range has a value that is appropriate during that timeframe.

How to create primary key on temporary table?

(Please note: As the #ABC table is an actual copy of a few of the columns in a “permanent” table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability: CAST (’01-01-1980′ AS DATETIME) AS [ReportRunTime] dbo.ABC; ). Any thoughts, references I missed, etc. are much appreciated.

When is temporal table referencing a foreign key?

In other words, when temporal table is referencing table in the foreign key relationship (corresponding to parent_object_id in sys.foreign_keys) CASCADE options are not allowed.

Which is the best indexing strategy for temporal data?

These are discussed later in this document in the section entitled Querying Temporal Data. An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance.