SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at element61, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.

How to drop a Temporal Table

No no, I didn’t say temporary, but temporal! SQL Server 2016 introduces a great new feature called Temporal Tables. Or in other words, system-versioned tables. We’ll see what catches on. In a gist, such a table keeps track of the history of its rows by the use of some audit columns (start and end date) and a history table. Sounds a bit like the love child of Change Data Capture and a Type 2 dimension.

Anyway, I was testing this feature a bit (more specifically if they supported computed columns as promised in CTP 2.1) and I created a temporal table with the following query:

CREATE TABLE dbo.TestTemporal
		(ID int primary key
		,A int
		,B int
		,C AS A * B
		,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
		,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL
		,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON);

This code only works in CTP2.1, not in earlier versions of SQL 2016 because of the computed column specification.
Anyway, of course I forgot to set my database so this table was created in the master database. I wanted to drop it, but I was greeted with this lovely message:

errormessage

Even the Delete action was missing from the table’s context menu.

nodelete

A quick look on the MSDN page Temporal Tables teaches us that dropping a system-versioned table is a disallowed Alter Schema operation. No luck there. The answer came, as usual, through Twitter:

Turning the system-versioning off and dropping the table is the answer to our question (and to about any schema operation you want to do on a system-versioned table). If we execute such an ALTER TABLE script, we get the following:

after_alter

The temporal table is reduced to a normal table, which means it can be dropped as a normal table. But the history table is kept as well. This means you can always re-enable system-versioning if you want to.
The name of the history table is dbo.MSSQL_TemporalHistoryFor_xxx, where xxx is the object id of the main table. Something to keep in mind if you want to automate scripts. Or just specify a friendly name when enabling system-versioning.

Comments

Leave a comment on the original post [blogs.lessthandot.com, opens in a new window]

Loading comments...