Temporal Table and History Table
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:
When creating a temporal table in Azure SQL Database, it automatically creates a corresponding history table to capture the historical records. Here are some statements regarding temporal table and history table:
A. A temporal table must have 1 primary key: This statement is true. A temporal table requires a primary key constraint to be defined.
B. To create a temporal table, System Versioning needs to be set to On: This statement is true. System Versioning needs to be enabled to create a temporal table in Azure SQL Database.
C. To create a temporal table, System Versioning needs to be set to Off: This statement is false. System Versioning must be enabled to create a temporal table in Azure SQL Database.
D. It is mandatory to mention the name of the history table when you create the temporal table: This statement is false. By default, the history table will be named with the schema name of the temporal table appended with "_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 the user does not specify the history table name when creating a temporal table, the default naming convention is used for the history table.
F. You can specify the table constraints for the history table: This statement is true. When creating a temporal table, users can define table constraints for both the temporal table and the history table.
In summary, when creating a temporal table in Azure SQL Database, users must enable System Versioning and define a primary key constraint. By default, the corresponding history table will be named with the schema name of the temporal table appended with "_history". Users can also define table constraints for both the temporal table and the history table.
