次の方法で共有

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

  • [アーティクル]

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:

 

[sql]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 )
)[/sql]

 

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:

 

temporal8

 

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

 

[sql]DROP TABLE [dbo].[TemporalTest][/sql]

 

Well that gives us this interesting looking error:

 

temporal9

 

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:

 

temporal10

 

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

 

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

 

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!

 

temporal11

 

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:

 

temporal12

 

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

 

[sql]

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 ))[/sql]

 

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

 

Links to other posts in this series SQL 2016 – Temporal Tables – What are they and how do you set one up? SQL 2016 – Temporal Tables – Can you change the schema?