I'm designing a database that will have history tables (SCD Type 4 I guess) for auditing purpose.
The question is what are the advantages of having tuple-versioning (start and end dates) vs just effective date?
1) tuple versioning
CREATE TABLE HistoryTable ( Column1 Type1, : : Columnn Typen, StartDate DATETIME, EndDate DATETIME )
2) effective date
CREATE TABLE HistoryTable ( Column1 Type1, : : Columnn Typen, EffectiveDate DATETIME )
For (1) so far I see disadvantages - I have to update [end date] on the previous history record while inserting new/current one.
For (2) I can only insert new record without searching and modification the previous one.
From query point of view both approaches look quite the same.
I know that SQL Server 2016 has temporal tables but it's not available to us so far.