Temporal Tables

Temporal tables, or system-versioned tables are new to SQL SERVER 2016 and are a way to have access to all of the history of a table.  In small databases or for tables where rows are rarely updated this feature is a beautiful thing and will soon find its way into many best practices.  To get started, simply right mouse on tables > New > Temporal Table > System-Versioned Table...

When creating a new Temporal Table, you are provided a template to generate the new table.  The first section of the given template is for dropping the table if it already exists.  Since this is a new table we can skip this part and go to the create table section.  *Remember, System versioning must be set to off in order to drop the table.
The second section of the template is for creating the system versioned temporal table. In order for a table to become a system versioned temporal table, we must have a Primary Key and 2 datetime2 colums.   The with clause of the create table statement turns system versioning on and defines the history table.
I used the following to populate my table.  I ran each insert and update statement separately so that I would have a noticeable time between each record.

USE [Test]
GO

INSERT INTO [dbo].[TemporalTest1]
 ([TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber])
 VALUES
 (1
 ,'First Record'
 ,1)
GO

INSERT INTO [dbo].[TemporalTest1]
 ([TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber])
 VALUES
 (2
 ,'Second Record'
 ,2)
GO

INSERT INTO [dbo].[TemporalTest1]
 ([TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber])
 VALUES
 (3
 ,'Third Record'
 ,3)
GO

UPDATE [dbo].[TemporalTest1]
SET [RandomNumber] = 9999
WHERE TemporalTest1_ID = 1

Now to see our results.

SELECT TOP (1000) [TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber]
 ,[period_start]
 ,[period_end]
 FROM [Test].[dbo].[TemporalTest1]

Here we can see our 3 records in their current state.

SELECT TOP (1000) [TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber]
 ,[period_start]
 ,[period_end]
 FROM [Test].[dbo].[TemporalTest1]
 FOR SYSTEM_TIME AS OF '2017-07-13 16:11:06.4594953'

Here we can see the records as of a specific time.

SELECT TOP (1000) [TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber]
 ,[period_start]
 ,[period_end]
 FROM [Test].[dbo].[TemporalTest1]
 FOR SYSTEM_TIME ALL

Here we can see all records that have been in the table.

SELECT TOP (1000) [TemporalTest1_ID]
 ,[TemporalTest1_Description]
 ,[RandomNumber]
 ,[period_start]
 ,[period_end]
 FROM [Test].[dbo].[TemporalTest1]
 FOR SYSTEM_TIME BETWEEN '2017-07-13 16:11:06.4594953' AND '2017-07-14'

Here we use the BETWEEN clause.  We can find all records that existed within a time range.

You can see how this can be a very power and easy to use tool for audit tracking.  The biggest draw back I have seen is that we can not use temporal tables with replication.  I suppose we will need to wait for Microsoft to fix this in a future version.