Temporal data tables in SQL Server


I came across a very interesting post about creating temporal tables in SQL Server and Azure SQL. In the past it was always a bit of a mission to set up a combination of tables that would track each change to the state of a particular row and allow you to browse through the history easily. 

Temporal tables

It turns out that now you can just declare such table using pure DDL, no triggers, no stored procedures. It is as simple as the following DDL instruction (taken from Peter's article):

Create Table dbo.SalesOrder   
(    
  ID int Not Null Primary Key Clustered,   
  ...remaining columns...
  StartDate datetime2 GENERATED ALWAYS AS ROW START HIDDEN,  
  EndDate datetime2 GENERATED ALWAYS AS ROW END HIDDEN,  
  PERIOD FOR SYSTEM_TIME (StartDate, EndDate)  
)    
WITH (SYSTEM_VERSIONING = ON 
      (HISTORY_TABLE = dbo.SalesOrderHistorical));

Bear in mind though. You should not implement this functionality for all tables in your database, because you can. Always gather detailed requirements from the users and implement temporal tables where they are needed, because temporal tables add additional storage and processing overhead to your database. 


SelectCompare can compare your data as long as you can connect to your data source and write a SELECT query. That's easy!

Write your queries with filters and aggregations and receive the comparison results in no time. Reuse the queries and comparison definitions - save time!

Try SelectCompare today!


Featured Products



Leave a comment


Please note, comments must be approved before they are published