Temporal tables in SQL Server provide a powerful way to track historical data changes. However, when it comes to clearing out data from these tables, the standard TRUNCATE statement doesn’t work the same as it does on normal (non-temporal) tables.

This article will guide you through the process of truncating a temporal table while maintaining its integrity.

Understanding Temporal Tables

Before we dive into truncation, let’s quickly review what temporal tables are. A temporal table in SQL Server is a type of user table designed to keep a full history of data changes. It consists of two parts:

1. The current table (also known as the current state table)
2. A history table that automatically stores previous versions of data rows

The Challenge with TRUNCATE

When you try to use the TRUNCATE command on a temporal table, you’ll encounter an error:

TRUNCATE TABLE TemporalTable;

Result:

Msg 13545, Level 16, State 1, Line 1
Error: Cannot truncate table 'TemporalTable' because it is not a supported operation on system-versioned temporal tables.

This is because the TRUNCATE operation doesn’t align with the concept of maintaining historical data in temporal tables.

Solution: Disabling System Versioning

To effectively truncate a temporal table, we need to follow these steps:

1. Disable system versioning
2. Truncate both the current and history tables
3. Re-enable system versioning

Let’s walk through this process with an example.

Example: Truncating a Temporal Table

First, let’s create a sample temporal table:

-- Create a temporal table
CREATE TABLE Employees
(
    EmployeeID INT PRIMARY KEY CLUSTERED,
    Name NVARCHAR(100),
    Position NVARCHAR(100),
    Salary DECIMAL(10, 2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeesHistory));

-- Insert sample data
INSERT INTO Employees (EmployeeID, Name, Position, Salary)
VALUES 
(1, 'Mickey Clooney', 'Manager', 75000),
(2, 'Donald Pitt', 'Developer', 65000),
(3, 'Minnie Jolie', 'Designer', 60000);

-- Update some data to generate history
UPDATE Employees SET Salary = 80000 WHERE EmployeeID = 1;
UPDATE Employees SET Position = 'Senior Developer' WHERE EmployeeID = 2;

Let’s check the contents of both tables (i.e. the current table and its history table):

-- Check current table
SELECT * FROM Employees;

-- Check history table
SELECT * FROM EmployeesHistory;

Result:

EmployeeID  Name            Position          Salary  ValidFrom                 ValidTo                 
----------  --------------  ----------------  ------  ------------------------  ------------------------
1           Mickey Clooney  Manager           80000   2024-07-27T06:39:18.522Z  9999-12-31T23:59:59.999Z
2           Donald Pitt     Senior Developer  65000   2024-07-27T06:39:18.534Z  9999-12-31T23:59:59.999Z
3           Minnie Jolie    Designer          60000   2024-07-27T06:39:18.522Z  9999-12-31T23:59:59.999Z


EmployeeID  Name            Position   Salary  ValidFrom                 ValidTo                 
----------  --------------  ---------  ------  ------------------------  ------------------------
1           Mickey Clooney  Manager    75000   2024-07-27T06:39:18.522Z  2024-07-27T06:39:18.522Z
2           Donald Pitt     Developer  65000   2024-07-27T06:39:18.522Z  2024-07-27T06:39:18.534Z

Good, both tables contain data.

Truncation Time!

Now, let’s truncate this temporal table:

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

-- Truncate both current and history tables
TRUNCATE TABLE Employees;
TRUNCATE TABLE EmployeesHistory;

-- Re-enable system versioning
ALTER TABLE Employees SET
(
    SYSTEM_VERSIONING = ON
    (
        HISTORY_TABLE = dbo.EmployeesHistory
    )
);

After executing these commands, both the current table (Employees) and the history table (EmployeesHistory) will be empty, effectively truncating the temporal table.

Verifying the Result

To confirm that the truncation was successful, we can run the following queries:

-- Check current table
SELECT * FROM Employees;

-- Check history table
SELECT * FROM EmployeesHistory;

Result:

0 row(s) returned
0 row(s) returned

Both queries return no rows, indicating that the truncation was successful.

Conclusion

While truncating a temporal table isn’t as straightforward as using the TRUNCATE command, it’s still achievable by temporarily disabling system versioning.

Remember to always be cautious when truncating tables, especially in production environments, as this operation permanently removes all data.