SQL 2016 - Temporal Tables - How do you drop a Temporal Table?

First published on MSDN on Jan 19, 2017
As we continue our journey with Temporal Tables, the next topic will be how do you drop them.  While this may seem like an odd thing to write about, dropping a Temporal Table does have a couple extra steps.



Let's begin by creating a temporal table:

CREATE TABLE [dbo].[TemporalTest](
[colint] [int] NOT NULL,
[period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PRIMARY KEY CLUSTERED 
(
[colint] ASC
)ON [PRIMARY],
PERIOD FOR SYSTEM_TIME ([period_start], [period_end])
) ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON )
)

Let's compare the menu options when we right click on a regular table in SSMS vs when we right click on a temporal table in SSMS:







Hmm, there is no "Delete" option for Temporal Tables.  What about if we simply run a TSQL Drop Table command?

DROP TABLE [dbo].[TemporalTest]

Well that gives us this interesting looking error:







Ok, so how do we drop a temporal table?  To find out, in SSMS right click on the Temporal Table, choose Script Table As and then choose Drop To and lastly choose New Query Editor Window:







Removing the comments, this is the code that I get:

ALTER TABLE [dbo].[TemporalTest] SET ( SYSTEM_VERSIONING = OFF)
GO
DROP TABLE [dbo].[TemporalTest]
GO
DROP TABLE [dbo].[TemporalTestHistory]
GO

If you run just the first line to turn off SYSTEM_VERSIONING and refresh your table list in SSMS, you'll notice that the temporal table and the history table become regular tables.  For fun, I re-ran the first line but changed SYSTEM_VERSIONING = OFF to SYSTEM_VERSIONING = ON and it auto created a new history table!







It auto created the history table because when we first created the temporal table, we defined the PERIOD FOR SYSTEM_TIME: PERIOD FOR SYSTEM_TIME ([period_start], [period_end]).  If you try to run the first line for a different regular table, you will likely see this error:







To change an existing table to a Temporal Table, ensure the table has a primary key and then run code like the following:

ALTER TABLE [schema].[tablename] ADD
[period_start] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
[period_end] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME ([period_start], [period_end])
 
ALTER TABLE [schema].[tablename] SET ( SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[TemporalTestHistory] , DATA_CONSISTENCY_CHECK = ON ))

That is all for today!  I hope you are enjoying this series on Temporal Tables!