Matthew McGiffen gets to one of my problems with temporal tables:
I also referred to Temporal Tables which are available to us from SQL Server 2016 onward.
Temporal tables aren’t just about monitoring change, they also provide really nice methods for being able to query historical data – to see what the values were at a particular point in time e.g.
SELECT * FROM dbo.SomeData FOR SYSTEM_TIME AS OF '1900-01-01';
My big problem with temporal tables is that they only implement system-defined times. That’s fine in a quasi-historical OLTP scenario, where you want to track history but only occasionally make use of it. But if they supported application time, then you have the ability to create something akin to a type-2 slowly changing dimension with just a few extra words. I understand that the tricky part is that application-defined temporal tables lose the nicety of knowing that the latest insert always goes into the main table and drives the prior record into the historical table, but there are some clever ways around this problem as well. It’s just too early in the morning for me to articulate them is all…