How to truncate temporal table

By Mirek on (tags: SQL Server, Temporal Tables, categories: code)

In this post I’ll show you simple script that can be used to clear all data from table with history versioning using temporal table.

Temporal table was introduced in Sql Server 2016 and serves an ideal solution when you need the data in your table to be versioned. However it can be a little cumbersome when your project is still in development and you fill in your table and its history with some test data. When you need to clean it up there is only one way to do so and of course its a T-SQL Winking smile

Assumming we have OrderLines table with its history table named OrderLinesHistory we can clean it up with following query:

ALTER TABLE [dbo].[OrderLines] SET (SYSTEM_VERSIONING = OFF)
TRUNCATE TABLE [dbo].[OrderLinesHistory];
TRUNCATE TABLE [dbo].[OrderLines];
ALTER TABLE [dbo].[OrderLines] SET (SYSTEM_VERSIONING = On (HISTORY_TABLE =  [dbo].[OrderLinesHistory], DATA_CONSISTENCY_CHECK = ON))  
GO

As you can see we basically truncate both tables as normal, but first we need to switch off the system versioning and turn it back on after cleaning.

That’s it.

Cheers