How do you drop a temporal table in SQL Server?

I'm trying to drop a temporal (system-versioned) table in SQL Server. I used a regular DROP TABLE statement.

DROP TABLE [schema].[table]
GO

This raised the following error:

Drop table operation failed on table '[database].[schema].[table]'
because it is not a supported operation on system-versioned temporal
tables.

How do you drop a temporal table in SQL Server?

To drop a system versioned table you first have to turn off system versioning.

CREATE TABLE [temporal]([id]        int IDENTITY(1, 1)
                                PRIMARY KEY CLUSTERED,
                       [ValidFrom] datetime2(7) GENERATED ALWAYS AS ROW START HIDDEN
                                        NOT NULL,
                    [ValidTo]   datetime2(7) GENERATED ALWAYS AS ROW END HIDDEN
                                        NOT NULL,
                    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])) WITH(SYSTEM_VERSIONING = ON(HISTORY_TABLE = [dbo].[Temporal_History]));

drop table temporal;

this will result in

Drop table operation failed on table 'tempdb.dbo.temporal' because it is not a supported operation on system-versioned temporal tables.

if you turn off system version you will be able to drop table. dont forget to drop the history table as well if you no longer need it

ALTER TABLE dbo.temporal  SET ( SYSTEM_VERSIONING = Off )

drop table temporal;
drop table Temporal_History

StackExchange Database Administrators Q#314887, answer score: 12