If you’re getting an error that reads something like “Truncate failed on table ‘test.dbo.ProductInventory’ because it is not a supported operation on system-versioned tables.” in SQL Server, it’s probably because you’re trying to truncate a system-versioned temporal table.

In SQL Server, if a table is defined as a temporal table, we can’t truncate it while it’s using system-versioning.

If you really want to truncate the table, turn off system-versioning first, then try again.

Another option is to use DELETE instead of TRUNCATE. Doing this clears the current table but keeps the history.

Example of Error

Here’s an example of code that produces the error:

TRUNCATE TABLE ProductInventory;

Result:

Msg 13545, Level 16, State 1, Line 1
Truncate failed on table 'test.dbo.ProductInventory' because it is not a supported operation on system-versioned tables.

This error occurred because the ProductInventory table is a system-versioned temporal table. As mentioned, we can’t truncate tables while they use system-versioning.

Solution 1

We need to disable system-versioning first. Once that’s done, we can go ahead and truncate the table.

Here’s an example:

-- Step 1: Disable system versioning
ALTER TABLE ProductInventory SET (SYSTEM_VERSIONING = OFF);

-- Step 2: Truncate both current and history tables
TRUNCATE TABLE ProductInventory;
TRUNCATE TABLE ProductInventoryHistory;

-- Step 3: Re-enable system versioning
ALTER TABLE ProductInventory 
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductInventoryHistory));

Here, I also truncated the history table, which is the table that keeps a record of all the changes in the current table.

If we still want to use the temporal features after truncating the table, we need to reenable system-versioning (which is Step 3 in our example above).

Solution 2

If you only want to clear the current data but keep the history, you could use a DELETE statement instead of TRUNCATE. This can be done without disabling system-versioning:

DELETE FROM ProductInventory;

Doing this will delete all rows from the ProductInventory table.

The history table will set its ValidTo time to the time it was deleted.