SQL Server system-versioned temporal tables provide a powerful way to keep data history for a table. It provides a way to query data as of a point in time, or the changes between a time period. Check the docs for more detail.
While history tables are great for many scenarios based off history data. When you need to calculate delta changes it gets a little more complicated as I am sure Temporal Tables were not designed for that purpose. In this case, delta means that over a period of time we want to get added records, changed records with calculation of differences, and deleted records.
To demonstrate how that works, here is a simplified example table with system versioning turned on:
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATETABLEdbo.Orders(IdbigintNOTNULLIdentity(1,1),CustomerIdbigintNOTNULL,--not really relevant for the example
TotalValuedecimal(18,6)NOTNULL,CONSTRAINTPK_OrdersPRIMARYKEYCLUSTERED(Id),)ALTERTABLEdbo.OrdersADDValidFromdatetime2GENERATEDALWAYSASROWSTARTHIDDENNOTNULLCONSTRAINTDF_Orders_ValidFromDEFAULT(SYSUTCDATETIME()),ValidTodatetime2GENERATEDALWAYSASROWENDHIDDENNOTNULLCONSTRAINTDF_Orders_ValidToDEFAULT('9999-12-31 23:59:59.9999999'),PERIODFORSYSTEM_TIME([ValidFrom],[ValidTo])ALTERTABLEdbo.OrdersSET(SYSTEM_VERSIONING=ON(HISTORY_TABLE=[dbo].[OrderHistory],HISTORY_RETENTION_PERIOD=1Year))
To get the wanted delta changes, we will need two dates as shown below. Note that both dates must be in UTC time zone as the ValidFrom and ValidTo columns will be saved in UTC too.
1
2
DECLARE@DeltaStartdatetime2-- for each run, this will be the value of @DeltaEnd of the previous run
DECLARE@DeltaEnddatetime2=getutcdate()-- easier to put a limit so we know exactly what the next @DeltaStart will be
Finding new and updated records is fairly straight-forward:
1
2
3
4
5
6
7
8
-- 1. Query the current record using ValidFrom and ValidTo to detect changes
-- 2. Left join to that same record as it was at the @DeltaStart, if the record was changed, updated won't be null
-- 3. Get columns from the current record and calculate ValueDiff using current.TotalValue - updated.TotalValue
SELECTcurrent.Id,current.CustomerId,current.TotalValue,Status=IIF(IsNull(updated.Id,0)=0,'INSERTED','UPDATED'),ValueDiff=current.TotalValue-IsNull(updated.TotalValue)FROMdbo.OrderscurrentLEFTJOINdbo.Ordersforsystem_timeasof@DeltaStartupdatedONupdated.Id=current.Idwherecurrent.ValidFrom>@DeltaStartandcurrent.ValidTo<=@DeltaEnd
Deleted records will be removed from the main system-versioned table (Orders). However, they will remain in the history table (OrderHistory). Thus, we can use the history table as the start and if the same record is not found in current table, it was deleted:
1
2
3
4
5
6
7
-- 1. Look into history records that were closed between @DeltaStart and @DeltaEnd
-- 2. Left join with current record, if the current is null, the record was deleted
SELECThistory.Id,history.CustomerId,history.TotalValue,Status='DELETED',ValueDiff=0-history.TotalValueFROMdbo.OrderHistoryhistoryLEFTJOINdbo.OrderscurrentONupdated.Id=current.IdwhereValidTo>@DeltaStartandValidTo<=@DeltaEndandcurrent.Idisnull
Let’s wrap this in a procedure to make it easier to consume:
-- for the first run, we have no way to get @DeltaStart so we just use some time before the insert
DECLARE@DeltaStartdatetime2=dateadd(minute,-1,getutcdate())INSERTINTOdbo.Orders(CustomerId,TotalValue)values(1,100),(2,100)DECLARE@DeltaEnddatetime2=getutcdate()execspGetOrderDeltaChanges@DeltaStart,@DeltaEnd-- don't forget to store the value of @DeltaEnd for the next run
Id
CustomerId
TotalValue
ChangeType
ValueDiff
1
1
100
INSERTED
100
2
2
100
INSERTED
100
This is self-explanatory, so let’s try to update record 1 and see the result:
1
2
3
4
5
6
7
8
9
DECLARE@DeltaStartdatetime2-- get the value from @DeltaEnd from the previous run
UPDATEdbo.OrdersSETTotalValue=200WHEREId=1DECLARE@DeltaEnddatetime2=getutcdate()execspGetOrderDeltaChanges@DeltaStart,@DeltaEnd
Id
CustomerId
TotalValue
ChangeType
ValueDiff
1
1
200
UPDATED
100
Note that record 2 was not touched so it won’t show on the delta results. Also, the ValueDiff column is only 100 as TotalValue was updated from 100 to 200. Now, let’s delete record 2 and see what happens:
1
2
3
4
5
6
7
8
DECLARE@DeltaStartdatetime2-- get the value from @DeltaEnd from the previous run
DELETEdbo.OrdersWHEREId=2DECLARE@DeltaEnddatetime2=getutcdate()execspGetOrderDeltaChanges@DeltaStart,@DeltaEnd
Id
CustomerId
TotalValue
ChangeType
ValueDiff
2
2
100
DELETED
-100
This time, we only show the deleted record. Note that the ValueDiff will be negative as the TotalValue changed from 100 to 0 (or nothing).
The use cases for this is limited, but though it was not explicitly designed for this, system-versioned tables can be used to calculate delta changes of a table’s data. The changes will accumulate from @DeltaStart to @DeltaEnd so however many transactions occur over this time period in dbo.Orders will be reported when the procedure is executed.