Skip to content
Home » Modifying Temporal Tables – A Primer

Modifying Temporal Tables – A Primer

  • by

This is part three of the Temporal Tables series. You can read parts one and two here.

Last week I demonstrated how temporal tables in SQL Server 2016 work. If you have implemented a history table—populating it with triggers or stored procedures—it works the same way.

This week, we are going to look at how to modify a temporal table that already has data in the history table.

Warning!

Modifying a temporal table can potentially break the audit trail.

In other words, you will be able to modify historic data. Be cognisant of this.

The safest way to control this is by securing the ALTER TABLE command in your production environment, using GRANT ALTER.

In our Account table example, let’s grant privileges to the Admin role (which must exist on the instance, of course).

-- Only allow a certain role to perform ALTER
GRANT ALTER ON OBJECT::[dbo].[Account] TO [AdminRole];
GO

With that in mind, we can continue.

Modify my temporal table

Let’s say we need to make the [AccountName] column in our example table longer, to support 2000 Unicode characters.

Temporal tables are linked to their history tables in a manner similar to views created with SCHEMABINDING.

This means that no changes can be made to either table without breaking the temporal link first.

To do this, we have to run an ALTER TABLE command with the SET SYSTEM_VERSIONING parameter set to OFF.

[Edit: The syntax changed for the latest CTP 3.3, and the command requires brackets around the SYSTEM_VERSIONING statement.]

-- Disable System Versioning on Account table
ALTER TABLE [dbo].[Account] SET (SYSTEM_VERSIONING = OFF);
GO

Now we can modify our primary table and history table with the same change.

-- Modify our primary and history tables
ALTER TABLE [dbo].[Account]
   ALTER COLUMN [AccountName] NVARCHAR(2000) NOT NULL;
ALTER TABLE [History].[Account]
   ALTER COLUMN [AccountName] NVARCHAR(2000) NOT NULL;
GO

Once we have made our modifications, we simply set the SYSTEM_VERSIONING parameter to ON and link back to our history table.

ALTER TABLE [dbo].[Account]
   SET (
      SYSTEM_VERSIONING = ON (
         HISTORY_TABLE = [History].[Account],
         DATA_CONSISTENCY_CHECK = ON
      )
   );
GO

The DATA_CONSISTENCY_CHECK parameter should be enabled, as recommended by Microsoft, to ensure that the history table follows the correct structure and format as the primary table.

Reminder

Because it’s that easy to make changes to temporal tables, it is just as easy to break the audit chain. Make sure you are not breaking any compliance rules.

If you have to change a temporal table, it may make more sense in your audit policy to create a new history table, active from that change, and retain the old history table untouched.

Next week we will look at when it is appropriate to use temporal tables and some factors to consider around making that choice.

Tags: