There are two ways to determine if a SQL table is temporal:
Check the table definition. Temporal tables have two system-versioned period columns: sys_start_time and sys_end_time. These columns store the start and end of the time period for which the row is valid. If a table has these two columns, then it is a temporal table.
Use the OBJECTPROPERTY() function. The OBJECTPROPERTY() function can be used to get various information about a database object, including whether it is a temporal table. The following SQL statement can be used to check if a table is temporal:
SELECT OBJECTPROPERTY(object_id('table_name'), 'IsTemporalTable');
If the result of the query is 1, then the table is temporal. Otherwise, the table is not temporal.
For example, the following SQL statement will check if the Products table is temporal:
SELECT OBJECTPROPERTY(object_id('Products'), 'IsTemporalTable');
If the result of the query is 1, then the Products table is temporal. Otherwise, the Products table is not temporal.
Note: Temporal tables are only supported in Microsoft SQL Server 2016 and later versions.
Here are some additional things to keep in mind about temporal tables:
- Temporal tables can be either system-versioned or history-table versioned. System-versioned temporal tables are managed by the database engine, while history-table versioned temporal tables are managed by the user.
- Temporal tables can be used to store both current and historical data. Current data is stored in the table, while historical data is stored in a history table.
- Temporal tables can be used to query both current and historical data. The FOR SYSTEM_TIME clause can be used to specify the time period for which the data should be returned.
If you are new to temporal tables, check out What are Temporal Tables in SQL Server.