SQL Server 2017 Developer???s Guide

Dropping temporal tables

You cannot drop a system-versioned temporal table. Both current and history tables are protected until the SYSTEM_VERSIONING attribute of the current table is set to ON. When you set it to OFF, both tables automatically become non-temporal tables and are fully independent of each other. Therefore, you can perform all operations against them that are allowed according to your permissions. You can also drop the period if you definitely want to convert a temporal table to a non-temporal one. The following code converts the Product table into a non-temporal table and removes the defined SYSTEM_TIME period:

ALTER TABLE dbo.Product SET (SYSTEM_VERSIONING = OFF);
ALTER TABLE dbo.Product DROP PERIOD FOR SYSTEM_TIME);

Note ...