Have you ever been asked to create a History/audit table for your database? Do you need to? If so, then read this blog post on the awesome feature now built into SQL 2016 and SQL Azure.
What is a temporal table?
A temporal table is a new type of user table in SQL Server 2016 and SQL Azure. These tables allow a point-in-time analysis by keep a full history of data changes, without the need of custom coding using triggers etc. You can create any new user table as a temporal table, or convert an existing table into a temporal table. By converting an existing table to a temporal table you will not need to do anything to any stored procedures or T-SQL statements to allow your application to continue working, it will just continue working, while storing the history data of any changes. These tables can also be known as system-versioned temporal tables because each row is managed by the system.
Every temporal table has two explicitly defined datetime2 columns. These columns are referred to as period columns and are used by the system to record period of validity for each row whenever a row is modified. A temporal table also has reference to another table with the same schema as itself. This is the history table and automatically stores the previous version of the row each time a row in the temporal table gets updated or deleted. This allows the temporal table to remain as the current table, and the history table to hold… well the history data. During temporal table creation users can specify existing history table (which must match the schema of the temporal table) or let the system create a default history table.
How does temporal work?
All current entries are stored within the Temporal table with a Start time and non-ending End time. Any changes will cause the original row to be stored in the history table with the start time and end time for the period for which is was valid.
Let me show you an example.
On first input on a row, the value would be entered only into the Temporal table.
Temporal
| ID | Value | StartTime | EndTime |
| 1 | My First Value | 2016-05-01 10:26:45.15 | 9999-12-31 23:59:59.99 |
History
| ID | Value | StartTime | EndTime |
On Update to ID 1, the original inputted value is entered into the history table with the EndTime updated to match when the Update took place, and Temporal table is updated with the updated value and the new start time.
Temporal
| ID | Value | StartTime | EndTime |
| 1 | My Second Value | 2016-05-14 14:54:44.54 | 9999-12-31 23:59:59.99 |
History
| ID | Value | StartTime | EndTime |
| 1 | My First Value | 2016-05-01 10:26:45.15 | 2016-05-14 14:54:44.54 |
On second update to ID 1, again the current value is entered into the history table with the EndTime updated to match when the Update took place, and the Temporal table is updated with the new version and new start time.
Temporal
| ID | Value | StartTime | EndTime |
| 1 | My Third Value | 2016-05-24 01:59:41.82 | 9999-12-31 23:59:59.99 |
History
| ID | Value | StartTime | EndTime |
| 1 | My First Value | 2016-05-01 10:26:45.15 | 2016-05-14 14:54:44.54 |
| 1 | My Second Value | 2016-05-14 14:54:44.54 | 2016-05-24 01:59:41.82 |
On deletion of ID 1, the current value is entered into the history table, with the EndTime updated to match when the row was deleted. The row is then removed from the Temporal table.
Temporal
| ID | Value | StartTime | EndTime |
History
| ID | Value | StartTime | EndTime |
| 1 | My First Value | 2016-05-01 10:26:45.15 | 2016-05-14 14:54:44.54 |
| 1 | My Second Value | 2016-05-14 14:54:44.54 | 2016-05-24 01:59:41.82 |
| 1 | My Third Value | 2016-05-24 01:59:41.82 | 2016-06-01 13:12:17.72 |
Creating or converting exiting table to a temporal table.
You can create a temporal table by specifying the Transact-SQL statements directly as show below. I recommend using SQL Management Studio 2016 which can be obtained and downloaded from here. You do not need a SQL Server license to install and use this, and it can be used with SQL Azure.
By using SQL Management Studio 2016, you can obtain the correct T-SQL by right clicking Tables > New > Temporal Table > System-Versioned Table..
I’m going to create an Employee Table.
CREATE TABLE dbo.Employee ( [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED , [Name] nvarchar(100) NOT NULL , [Position] varchar(100) NOT NULL , [Department] varchar(100) NOT NULL , [Address] nvarchar(1024) NOT NULL , [AnnualSalary] decimal (10,2) NOT NULL -- This point below is the Period/Temporal set up on the table. , [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)); GO
If I was going to convert my existing Employee Table into a Temporal table, I would use the following T-SQL Statement
ALTER TABLE Employee
ADD
ValidFrom datetime2 (0) GENERATED ALWAYS AS ROW START HIDDEN
constraint DF_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME())
, ValidTo datetime2 (0) GENERATED ALWAYS AS ROW END HIDDEN
constraint DF_ValidTo DEFAULT '9999.12.31 23:59:59.99'
, PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE Employee
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO
AS you can see from above, SQL Management Studio indicates the System-Versioned and History table.
Inserts, updates and deleting data
When you come to doing your Inserts, Updates and Deletes there are no changes to T-SQL code, you would perform all against the Temporal table, (Employee table in my case). The T-SQL code below is demo code, that inserts 3 people a minute apart and then every 5 minutes something else will happen to the data. Either an update, inserting a new record, or delete.
--Create Lisa Fane INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary]) VALUES (1234,'Lisa Fane','Sales Rep','Sales','Hertforshire', 25000) GO WAITFOR DELAY '00:01' --Create Dan Wilson INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary]) VALUES (2435,'Dan Wilson','Developer','Development','Kent', 35500) GO WAITFOR DELAY '00:01' --Create David Hamilton INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary]) VALUES (3445,'David Hamilton','Developer','Development','Croydon', 20000) GO WAITFOR DELAY '00:05' --Update Lisa Fane with new job title and payrise. UPDATE [dbo].[Employee] SET [Position] = 'Lead Sales Rep',[AnnualSalary] = 32000 WHERE EmployeeID = 1234 GO WAITFOR DELAY '00:05' -- Give Lisa Fane a Pay Rise. UPDATE [dbo].[Employee] SET [AnnualSalary] = 33000 WHERE EmployeeID = 1234 GO WAITFOR DELAY '00:05' -- Give Dan Wilson a new job title and payrise UPDATE [dbo].[Employee] SET [Position] = 'Development Manager', [AnnualSalary] = 45500 WHERE EmployeeID = 2435 GO WAITFOR DELAY '00:05' --Employ Lucy Williamson INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary]) VALUES (8875,'Lucy Williamson','Project Management','PMO','Sutton', 20000) GO WAITFOR DELAY '00:05' --Lisa Fane change address UPDATE [dbo].[Employee] SET [Address] = 'Barnet' WHERE EmployeeID = 1234 GO WAITFOR DELAY '00:05' --Adam Crane joins the team INSERT INTO [dbo].[Employee] ([EmployeeID],[Name],[Position],[Department],[Address],[AnnualSalary]) VALUES (4454,'Adam Crane','Sales Rep','Sales','Islington', 26000) GO WAITFOR DELAY '00:05' --David Hamilton has a payrise UPDATE [dbo].[Employee] SET [Position] = 'Manage Services',[AnnualSalary] = 20500 WHERE EmployeeID = 3445 GO WAITFOR DELAY '00:05' --Lucy Williamson left the company. Delete From Employee Where EmployeeID = 8875
Running the above script takes about 30-35 mins.
Querying Temporal data
To obtain the current information in the Temporal table, there is no changes to your typical SQL Select statements.
SELECT * FROM Employee
As you can see from the above image, the results are as if it’s not a temporal table.
To view history data, there is a new clause you can use within the SELECT FROM statement. This is the FOR SYSTEM_TIME clause with 5 temporal-specific sub-clauses to query data across the current and history tables. This new SELECT statement syntax is supported directory on a single table, propagated through multiple joins, and through views on top of multiple temporal tables.
View All Data
Use the following command to see current and past records, the IsActual flag indicates if the row is current. This query is also useful as a view so that BI tools such as PowerBI can display a graph over time.
SELECT Name, Position, Department, [Address], AnnualSalary, ValidFrom, ValidTo, IIF (YEAR(ValidTo) = 9999, 1, 0) AS IsActual FROM Employee FOR SYSTEM_TIME All ORDER BY ValidFrom
Between two dates
Using BETWEEN <startDateTime> AND <endDateTime> will return rows that were active at least for a portion of period between the two times.
SELECT * FROM Employee FOR SYSTEM_TIME BETWEEN '2016-06-18 10:27:00' AND '2016-06-18 10:47:00' ORDER BY ValidFrom
Contains two dates
Using CONTAINS IN (<startDateTime>,<EndDateTime>) will return rows that were only active within a period (and not outside it). This only queries the history table. As you can see below Lisa Fane was updated 3 times within the time period.
SELECT * FROM Employee
FOR SYSTEM_TIME CONTAINED IN ('2016-06-18 10:25:00', '2016-06-18 11:50:00')
ORDER BY ValidFrom
Point in time search
Using AS OF <dateTime> will return how the database looked at that given moment in time. Below are multiple statements which returns results from those points in the database. I’ve highlighted within the next result set what has changed. This type of query is perfect for BI tools such as Power BI to query the data 24 hours, 7 days, 30 day etc.
SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 10:28:00' ORDER BY EmployeeID SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 10:31:00' ORDER BY EmployeeID SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 10:36:00' ORDER BY EmployeeID
SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 10:46:00' ORDER BY EmployeeID SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 10:56:00' ORDER BY EmployeeID SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 11:01:00' ORDER BY EmployeeID SELECT * FROM Employee FOR SYSTEM_TIME AS OF '2016-06-18 11:06:00' ORDER BY EmployeeID
References
I found quite a bit of good information available to help me understand Temporal tables, I have listed the main sites below.
Temporal Tables – https://msdn.microsoft.com/en-IN/library/dn935015.aspx
Getting Started with Temporal Tables in Azure SQL Database – https://azure.microsoft.com/en-us/documentation/articles/sql-database-temporal-tables/
Getting Started with System- Versioned Temporal Tables – https://msdn.microsoft.com/en-us/library/mt604462.aspx
Temporal in SQL Server 2016 (Video) – https://channel9.msdn.com/Shows/Data-Exposed/Temporal-in-SQL-Server-2016
SharePoint and other geeky stuff 