If you’re getting an error that reads something like “Drop table operation failed on table ‘db.dbo.TableName’ because it is not a supported operation on system-versioned temporal tables” in SQL Server, it’s probably because you’re trying to drop a temporal table that still uses system-versioning.

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

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

Example of Error

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

DROP TABLE ProductInventory;

Result:

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

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

Solution

The solution is to disable system-versioning first. Once that’s done, we can go ahead and drop the table.

Here’s an example:

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

-- Step 2: Drop both the current and history tables
DROP TABLE ProductInventory;
DROP TABLE ProductInventoryHistory;

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