With SQL Server 2016 we have a few new features which are also available in Standard Edition, such as temporal tables;
This is found in the SQL Server – What’s new PDF available at http://download.microsoft.com/download/8/A/2/8A2BC8C5-BBA0-4A9C-90BC-AC957D3454D9/SQL_Server_2016_Editions_datasheet.pdf
Temporal tables are system-versioned tables that allows you to see the data stored in a table at any point in time including all previous changes as opposed to just the latest version of the data in that table.
To craete a tabel with the temporal tables feature we issue the folllowing T-SQL;
CREATE TABLE dbo.Company
(
ID int IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
CompanyName VARCHAR(50) NOT NULL,
Telephone VARCHAR(22) NOT NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.CompanyHistory)
);
Next insert some data:
INSERT INTO [dbo].[Company] (CompanyName, Telephone) VALUES (‘sqltomato’,’013112345678′),(‘cheese inc.’,’020712345678′);
And look at the data:
SELECT * FROM [dbo].[Company];
And update a row:
UPDATE [dbo].[Company] SET Telephone = ‘00353123456789’ WHERE CompanyName = ‘sqltomato’;
Now lets look at all the versions of the table. For this we use SYSTEM_TIME and specify, in our example, start and end datetime.
SELECT * FROM [dbo].[Company] FOR SYSTEM_TIME BETWEEN ‘2016-05-16’ AND ‘2016-05-17’;
You can read more about temporal tables on this MSDN link https://msdn.microsoft.com/en-GB/library/dn935015.aspx