Temporal Tables in SQL Server 2016 – Part 1

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’;

And the results are as expected, we can see two versions and their applicable dates for the updated row below:



You cannot just drop a temporal table, you need to first drop the versioning. Once you have done that you have two standard tables remaining which can be dropped like any other object.
ALTER TABLE [dbo].[Company] SET (SYSTEM_VERSIONING = OFF); 
DROP TABLE [dbo].[Company];
DROP TABLE [dbo].[CompanyHistory];
 


You can read more about temporal tables on this MSDN link https://msdn.microsoft.com/en-GB/library/dn935015.aspx

Temporal Tables in SQL Server 2016 – Part 1

Leave a Reply

Your email address will not be published.

Pin It on Pinterest