This is the fourth article in the series of articles on the Temporal Tables, below is the complete list of articles on System-Versioned Temporal Tables. This article will cover DDL operations on the System-Versioned Temporal Table with extensive list of examples

• Part 1: Introduction to Temporal Tables and DML operations on the Temporal Table
• Part 2: Querying System-Versioned Temporal Table
• Part 3: Enabling System Versioning for an Existing Regular Table
• Part 4: DDL operations on the System-Versioned Temporal Table

Let us create a Temporal Table by using the following script:

 
--Create demo database if it is not present
IF DB_ID('SqlhintsTemporalDemoDB') IS NULL
BEGIN   
    CREATE DATABASE SqlhintsTemporalDemoDB
END
GO
USE SqlhintsTemporalDemoDB
GO
--Create System-Versioned Temporal Table
CREATE TABLE dbo.Customer 
(  
  Id INT NOT NULL PRIMARY KEY CLUSTERED,
  Name NVARCHAR(100) NOT NULL, 
  StartTime DATETIME2 GENERATED ALWAYS AS ROW START
            HIDDEN NOT NULL,
  EndTime   DATETIME2 GENERATED ALWAYS AS ROW END 
            HIDDEN NOT NULL,
  PERIOD FOR SYSTEM_TIME (StartTime, EndTime)   
) 
WITH(SYSTEM_VERSIONING= ON (HISTORY_TABLE=dbo.CustomerHistory))

The above script will create the Temporal Table dbo.Customer and the corresponding History Table dbo.CustomerHistory as shown in the following image

Let us try to add a column City to the temporal table by executing the following script

ALTER TABLE dbo.Customer ADD City NVARCHAR(50) 

Post executing the above statement, check the columns in the Temporal and History table

We can see that adding a column to the Temporal Table adds the column to the History Table too

Let us try to drop the newly added column by the following script

ALTER TABLE dbo.Customer DROP COLUMN City

Post executing the above statement, check the columns in the Temporal and History table

We can see that dropping a column from the Temporal Table drops the column from the history table too

Let us try to add a column directly to the History table by executing the following script

ALTER TABLE dbo.CustomerHistory ADD Country NVARCHAR(50)

RESULT

Msg 13550, Level 16, State 1, Line 1
Add column operation failed on table ‘SqlhintsTemporalDemoDB.dbo.CustomerHistory’ because it is not supported operation on system-versioned temporal tables.

From the above result we can see that when SYSTEM-VERSIONING is ON we can’t perform DDL operations on the HISTORY table but we can add indexes and default constraint on the History table.

History table can’t have constraints like : Primary key, foreign key, table or column constraints. Properties like Period definition, Identity definition, Indexes, Statistics, Check Constraints, Triggers, Permission, Partitioning Configuration is not replicated from the temporal/current table to the history table when it is created.

Below image shows that the primary key is not replicated from the Temporal Table to History Table when it is created.

One more important point to note that the history table by default is page compressed

Dropping a Temporal Table

Let us try to drop a Temporal Table by executing the below statement

DROP TABLE dbo.Customer

RESULT

Msg 13552, Level 16, State 1, Line 1
Drop table operation failed on table ‘SqlhintsTemporalDemoDB.dbo.Customer’ because it is not supported operation on system-versioned temporal tables.

From the above result we can see that when SYSTEM-VERSIONING is ON we can’t drop a Temporal table

Disabling System Versioning

We can disable system versioning by executing the following statement:

ALTER TABLE dbo.Customer SET (SYSTEM_VERSIONING = OFF)

After executing the above statement the System Versioning is disabled and Temporal and History table will become like any other regular table as shown in the following image:

Now try to update the Period columns in the dbo.Customer table

UPDATE dbo.Customer
SET 	StartTime = GETUTCDATE()
WHERE Id = 1

RESULT

Msg 13537, Level 16, State 1, Line 19
Cannot update GENERATED ALWAYS columns in table ‘SqlhintsTemporalDemoDB.dbo.Customer’.

From the above result it is clear that even after disabling the SYSTEM_VERSIONING, we will not be able to update the PERIOD column values. To allow the PERIOD column value update we have to drop the PERIOD definition by executing the following statement

ALTER TABLE dbo.Customer
DROP PERIOD FOR SYSTEM_TIME

Now once again try updating by executing the following script

UPDATE dbo.Customer
SET 	StartTime = GETUTCDATE()
WHERE Id = 1

RESULT

Now try to drop the dbo.Customer and dbo.CustomerHistory tables by executing the following script:

DROP TABLE dbo.Customer
GO
DROP TABLE dbo.CustomerHistory

RESULT

In Summary below is the script to drop System-Versioned Temporal Tables

--First disable the system versioning
ALTER TABLE dbo.Employee
    SET (SYSTEM_VERSIONING = OFF)
GO
--Drop Period definition.
--This will allow manipulations on the PERIOD columns
ALTER TABLE dbo.Employee
DROP PERIOD FOR SYSTEM_TIME
GO
--Now drop Employee and EmployeeHistory tables
--(To drop these tables, dropping PERIOD definition is optional)
DROP TABLE dbo.Customer
DROP TABLE dbo.CustomerHistory