SQL server 2016 introduces out of box support for working with temporal data. Custom implementations were possible in previous versions, however manageability was an issue. One use case I’ve seen for temporal tables in the field was tracking employee records. The business needs a way to trend employment status i.e.‘Terminated’, or ‘Active’. We will briefly examine a few ways to solve this problem before divulging into temporal tables.
1. Implement a slowly changing dimension design (SCD)
Slowly changing dimension are most frequently used in data warehouse design. A SCD type 2 would meet the trending requirements described above. A type two change tracks changes by inserting a new row anytime an update occurs and then modifies a predefined date column. Here’s how the table structure might look:
When the business wants to query only active employees they filter EmploymentStatus to ‘Active’ and the Valid_To clause to NULL. SCD design patterns provide an excellent base for analytical applications, however a couple drawbacks:
- It takes a skilled database developer to implement and support SCD
- Requires a surrogate key to maintain referential integrity
Anytime a row is modified ala update, delete, or insert we have the option to fire an action to perform an audit action. For example, when Matt was terminated on the 16th and his record changed from ‘Active’ to ‘Terminated’. The change can be logged to an audit table using an AFTER UPDATE trigger. Here’s the T-SQL implementation:
CREATE TABLE dbo.Employee(EmployeeID INT,FirstName VARCHAR(20),LastName VARCHAR(25),EmploymentStatus VARCHAR(10)) GO INSERT INTO dbo.Employee (EmployeeID,FirstName,LastName,EmploymentStatus) VALUES (12345, 'Matt' , 'Sharkey', 'Active'), (55123, 'Gus' , 'Johnson', 'Active'), (33612, 'Juilia' , 'Smith', 'Active') CREATE TABLE dbo.AuditTable (EmployeeID INT, EmploymentStatusOld VARCHAR(10), EmploymentStatusNew VARCHAR(10), DateModified DATETIME) GO CREATE TRIGGER dbo.AuditEmployeeStatus ON dbo.Employee AFTER UPDATE AS INSERT INTO dbo.AuditTable (EmployeeID,EmploymentStatusOld,EmploymentStatusNew,DateModified) SELECT d.EmployeeID, d.EmploymentStatus, I.EmploymentStatus,GETDATE() FROM deleted AS d JOIN Inserted AS I ON I.EmployeeID = d.EmployeeID
When we run the update the trigger fires and logs it to our audit table
Triggers are quick and dirty but face problems at scale. One can improve performance and manageability of the audit by processing record updates through stored procedures. The trigger above could be written as:
CREATE PROCEDURE dbo.Mod_Log_EmployeeStatus @employeeID INT, @EmploymentStatus Varchar(10) AS BEGIN TRY BEGIN TRANSACTION INSERT INTO dbo.AuditTable SELECT employeeID,EmploymentStatus, @EmploymentStatus,GETDATE() FROM dbo.employee WHERE EmployeeID = @employeeID UPDATE dbo.Employee SET EmploymentStatus = @EmploymentStatus WHERE employeeID = @employeeID COMMIT TRAN END TRY BEGIN CATCH ROLLBACK END CATCH
Let’s see what happens after we drop the trigger and then re-hire Matt.
Auditing is maintained without the overhead and idiosyncrasies associated with triggers.
Implementing change tracking using temporal tables gives the business stronger analytical potential then the previous solutions. The theory of temporal data has deep roots. Interested readers should google the famous white paper “Maintaining knowledge about temporal intervals” by James Allen and the wikepedia on Interval algebra http://bit.ly/2fpZuam
We need to add a few things to our table to make it work including a Primary Key, two DateTime2 columns and a WITH SYSTEM_VERSIONING = ON clause.
CREATE TABLE dbo.Employee ( EmployeeID INT PRIMARY KEY CLUSTERED , FirstName VARCHAR(20), LastName VARCHAR(25), EmploymentStatus VARCHAR(10) , [ValidFrom] datetime2 (2) GENERATED ALWAYS AS ROW START , [ValidTo] datetime2 (2) GENERATED ALWAYS AS ROW END , PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
The employee audit table was automatically created in the background.
Now let’s import our records into the newly defined table and recreate an update procedure. Notice we were able to reduce the amount of code per the versioning is handled by the system internally.
INSERT INTO dbo.Employee (EmployeeID,FirstName,LastName,EmploymentStatus) VALUES (12345,'Matt' , 'Sharkey','Active'), (55123,'Gus' , 'Johnson','Active'), (33612,'Juilia' , 'Smith','Active') CREATE PROCEDURE dbo.Mod_Log_EmployeeStatus @employeeID INT, @EmploymentStatus Varchar(10) AS UPDATE dbo.Employee SET EmploymentStatus = @EmploymentStatus WHERE employeeID = @employeeID
Let's go ahead and terminate Matt, then check the history using special temporal syntax.
We see the ability to traverse the table at a specific point in time is possible.
Let’s check the entire history using FOR SYSTEM_TIME ALL, this simply returns all current records and all changes since inception.
Now, imagine an errant transaction was ran against the table. Let’s go ahead and forget to add a WHERE clause to the employee table to illustrate:
We can fix the mess up using the BETWEEN operation or an AS OF operation. Set our system time to right before the transaction occurred and we are restored to the correct state of the data.
We have only just started to scratch the surface of temporal applications. I look forward to seeing this feature evolve.