The use cases for CDC and temporal tables are different. CDC is usually useful to keep changes for a short period of time to feed those to external consumers such as an ETL process.
Thanks much for the feedback and for writing!
Regarding your solution, there's no real need for row numbers to uniquely identify rows; you can rely on the id column....
With the following sample data:
TRUNCATE TABLE dbo.Transactions;
INSERT INTO dbo.Transactions(txid, val)
Martin, here you go:
SELECT keycol, from_ts, to_ts, sgn, y, m, d,
s / 3600 AS h,
s % 3600 / 60 AS mi,
s % 60 AS s,
(1000 + DATEPART(ms, to_ts) -...
This solution is absolutely brilliant!
Here are the stats that I got on my system (compared with two other solutions):
Itzik Double Negative: CPU time = 3875 ms,...