Temporal Table and History Table in Azure SQL Database | Exam DP-203 Microsoft
Question
When you create a temporal table in Azure SQL Database, it automatically creates a history table in the same database for capturing the historical records.
Which of the following statements are true about the temporal table and history table? [Select all options that are applicable]
Answers
Explanations
Click on the arrows to vote for the correct answer
A. B. C. D. E. F.Correct Answers: A, B and E
Here are some key points to note before creating the temporal table: A temporal table must have 1 primary key.
The period for system time must be defined with appropriate valid from and to fields with datetime2 datatype.
Set System Versioning to ON.
If you don't specify the name for the history table, the default naming convention is used for the history table.
Other optional parameters such as data consistency check and retention period etc can be declared in the syntax; if needed.
The history table is page compressed.
history table can't have any table constraints.
Option A is correct.
A temporal table must have 1 primary key.
Option B is correct.
To create a temporal table, System Versioning needs to be set to On.
Option C is incorrect.
To create a temporal table, System Versioning needs to be set to On, not Off.
Option D is incorrect.
It is up to you to specify the name for the history table or not.
Option E is correct.
If you don't specify the name for the history table, the default naming convention is used for the history table.
Option F is incorrect.
The history table can't have any table constraints.
Although you can create statistics or indexes to optimize the performance.
To know more about temporal tables, please visit the below-given link:
A temporal table is a new feature introduced in SQL Server 2016 and Azure SQL Database. It is used to keep track of the historical data in a database by creating a history table alongside the main table. The history table stores the previous versions of the rows that have been changed in the main table over time. When you query the temporal table, you can retrieve the data from the current version of the table as well as from the history table to get a complete picture of the data changes.
Here are the answers to the questions:
A. A temporal table must have 1 primary key.
This statement is true. A temporal table must have at least one primary key constraint defined on it. A primary key is required to uniquely identify each row in the table.
B. To create a temporal table, System Versioning needs to be set to On.
This statement is true. To create a temporal table, you need to set the System Versioning property to On. System Versioning is a feature that tracks the changes made to a table over time by creating a history table alongside the main table.
C. To create a temporal table, System Versioning needs to be set to Off.
This statement is false. You cannot create a temporal table with System Versioning set to Off. System Versioning is a required property for creating a temporal table.
D. It is mandatory to mention the name of the history table when you create the temporal table.
This statement is false. If you don't specify a name for the history table, a default naming convention is used for the history table. The default naming convention is <original_table_name>_History.
E. If you don't specify the name for the history table, the default naming convention is used for the history table.
This statement is true. If you don't specify a name for the history table, a default naming convention is used for the history table. The default naming convention is <original_table_name>_History.
F. You can specify the table constraints for the history table.
This statement is true. You can specify table constraints, such as primary key, foreign key, unique, and check constraints, on the history table just like any other table in the database. These constraints help ensure the consistency and integrity of the data in the history table.
