SQL Server 2016: Temporal Tables

chart-35796_640

System-Versioned Temporal Tables

For as long as there have been relational databases, there have been administrators who have needed to maintain a history table of row changes. There are many reasons for this; probably the most common is the requirements for auditing data if a particular row value is brought into question. More recently, there are slowly changing dimensions that need to be tracked in data warehouses. The traditional method for maintaining such history tables has been to use triggers, and this remains the preferred method if additional data, such as the user responsible for the changes, needs to be tracked for auditing purposes. However, for simply maintaining a history of row changes, the system-versioned temporal tables introduced in SQL Server 2016 provides a direct, automatic method.

A table of row changes can also provide a fast alternative to a restore in the event of accidental corruption of some row data.

Another, and perhaps less obvious, role of temporal tables is as a part of Microsoft’s new emphasis on real-time analytics. For some data, the temporal row store can provide additional value for analysts and statisticians.

Unless you prefer to do it yourself, SQL Server 2016 will automatically create a history table with the same columns as the main table (without constraints) and record the starting time and ending time that any particular set row values was valid.

The history table associated with a newly defined temporal appears in the Object Explorer and can be queries like any other table.

AutomaticallyGeneratedTemporalTable

If you do not provide SQL Server with a name for the history table, the automatically generated table name will contain the ObjectID number for the main table, which you can confirm using the OBJECT_NAME function.

SELECT OBJECT_NAME(1989582126)

OBJECT_NAME

Creating a System-Versioned Temporal Table

A system-versioned table can be created, or an existing table can be converted into a system-versioned temporal table. In either case, two new columns must be created to store the date and time the row data became valid and the date and time it was rendered invalid by a change of existing data. The declaration of these two columns must be associated with a PERIOD statement that specifies to the SQL Server engine the fields that correspond to the valid start and the valid end periods for the row data.

 

It is possible to define the columns without creating the temporal machinery.

CREATE TABLE dbo.EmployeesTemporalTest01(

EmployeeID int IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED

, LastName varchar(20) NOT NULL

, FirstName varchar(10) NOT NULL

, Title varchar(30) NULL

, TitleOfCourtesy varchar(25) NULL

, Gender char(1) NULL

, BirthDate datetime NULL

, HireDate datetime NULL

, Address varchar(60) NULL

, City varchar(15) NULL

, Region varchar(15) NULL

, PostalCode varchar(10) NULL

, Country varchar(15) NULL

, HomePhone varchar(24) NULL

, Extension varchar(4) NULL

, ReportsTo int NULL

, CurrentSalary money NOT NULL

, DivisionID numeric(5, 0) NULL

, DepartmentID numeric(5, 0) NULL

, ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START

, ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

)

In such a case, we could turn on system-versioning with an ALTER TABLE statement.

ALTER TABLE dbo.EmployeesTemporalTest01 SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory))

In this example, we have told SQL Server to use the name “EmployeeHistory” for the generated history table. We could also provide the name of an existing history table as long as the columns match.

Curiously, the Management Studio does not seem to fully recognize the syntax for these definitions and decorates them with red squiggles. The statements run successfully, however.

Modifying and Dropping System-Versioned Temporal Tables

Functioning temporal tables cannot be modified or dropped. This why it is important to be able to specify an existing history table when we turn system-versioning on. To change a temporal table, we must first turn system-versioning OFF.

ALTER TABLE dbo.EmployeesTemporalTest01 SET (SYSTEM_VERSIONING = OFF);

At this point, both the main table and its history table are plain-vanilla tables and can be modified as usual. After the changes have been made, system versioning can be turned back on. Of course, matching changes must be made to both tables if you wish to continue to use the existing history table.

CannotDropTemporalTables

Converting an Existing Table to System-Versioned

It is likely that many admins will wish to convert existing tables into system versioned tables. This is easy to accomplish, but it is necessary to add the new columns and provide the PERIOD specification in a single ALTER TABLE statement. The “GENERATED ALWAYS” portion of the declaration cannot be included if the ALTER TABLE statement does not provide the PERIOD statement as well.

ALTER TABLE EmployeesTemporalTest03

ADD

ValidFrom datetime2 (2) GENERATED ALWAYS AS ROW START

, ValidTo datetime2 (2) GENERATED ALWAYS AS ROW END

, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)

GO

After defining the new columns, we can turn system-versioning ON.

ALTER TABLE dbo.EmployeesTemporalTest03 SET (SYSTEM_VERSIONING = ON);

Conclusion

Whether your goal is providing a quick recovery mechanism for damaged row data, a slowly changing dimension in your data warehouse, or enhancing real-time analytic options, the system-versioned temporal table is very quick and easy to implement and to maintain.

If you would like to run the example SQL, it is available in a script file here.

Type to search blog.learningtree.com

Do you mean "" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.