Business Use Cases For Temporal Tables

Bert Wagner walks through some of the business cases for using temporal tables:

I know this query seems lame — it’s just a SELECT FROM statement. There are no FOR SYSTEM TIME clauses, WHERE statements, and no other interesting T-SQL features.

But that’s the point! Have you ever had to get the “current” rows out of a table that is keeping track of all transactions? I’m sure it involved some GROUP BY statements, some window functions, and more than a few cups of coffee.

Temporal tables automatically manage your transaction history, providing the most current records in one table (dbo.CarInventory) and all of the historical transactions in another (dbo.CarInventoryHistory). No need for complicated queries.

Temporal tables definitely have their uses.  At present, those uses are primarily around versioned fact data.

Related Posts

Querying Data In Temporal Tables

Jeanne Combrinck shows us how to query data stored in temporal tables: When you want to get latest (actual) state of data in a temporal table, you can query the same way as you query a normal table. If the PERIOD columns are not hidden, their values will appear in a SELECT * query. If […]

Read More

Modifying Data In Temporal Tables

Jeanne Combrinck shows us how we can insert, update, and delete data in temporal tables: You delete data in the current table with a regular DELETE statement. The end period column for deleted rows will be populated with the begin time of underlying transaction. You cannot directly delete rows from history table while SYSTEM_VERSIONING = […]

Read More

Categories

June 2017
MTWTFSS
« May Jul »
 1234
567891011
12131415161718
19202122232425
2627282930