Temporal tables, introduced in SQL Server 2016, provide a powerful way to track historical data changes. However, there may come a time when you need to remove a temporal table from your database.

This article will guide you through the process of dropping a temporal table in SQL Server.

Understanding Temporal Tables

Before we dive into dropping temporal tables, it’s important to understand their structure. A temporal table consists of two parts:

1. The current table (also known as the current state table)
2. A history table that stores historical data

Steps to Drop a Temporal Table

1. Disable system versioning: Before you can drop a temporal table, you must first disable system versioning. This separates the current table from its history table.
2. Drop the current table: Once system versioning is disabled, you can drop the current table using the standard DROP TABLE command.
3. Drop the history table: The history table, which typically has a name suffix of History, needs to be dropped separately.

Example

Here’s an example that demonstrates the whole process described above:

-- Disable system versioning
ALTER TABLE dbo.Employees
SET (SYSTEM_VERSIONING = OFF);

-- Drop the current table
DROP TABLE dbo.Employees;

-- Drop the history table
DROP TABLE dbo.EmployeesHistory;

This code drops a temporary table called Employees and its history table called EmployeesHistory.

Important Considerations

• Ensure you have the necessary permissions to alter and drop tables in your database.
• Dropping a temporal table will permanently delete all historical data. Make sure to back up any important information before proceeding.
• If you only want to temporarily disable the temporal feature, you can use the SET (SYSTEM_VERSIONING = OFF) command without dropping the tables.

By following these steps, you can successfully drop a temporal table in SQL Server, removing both the current state and historical data from your database.