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.
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
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
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
[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
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.
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.