schleep (8/27/2015)

---

You're now technical talking about *bi-temporal support*, not just temporal. As in - in order to provide the correct history in your OLTP, you will need to keep track of the effectivity of the change (when does the change become effective) and the date you specifically processed the change.

When you say "date ... processed the change", do you mean the date the change comes into effect, or the date the rows were inserted with a future effective/expiry date?

I would caution against trying to run OLTP against a true SCD 2 setup. As in - it's certainly okay to record changes you make to the "live" table and convey those in SCD form to a table with a similar layout in the same local DB. But keeping the very high level of changes inside of the operational 9while maintaining bi-temporal support AND tracking whether these changes are in flight or final, etc...) is going to thoroughly thrash your data access.

That's what I'm afraid of.

there really isn't a concept of "update" or "delete" in SCD - *new* entries are created any time ANY change occurs. the only update tolerated would be the part that expires the previous version of the record you just updated.

Hadn't thought of that: it might be the proverbial nail in the coffin. We rely on single row updates to make n rows "visible", where 1 > n > ~4600.

This would result in n updates + n inserts.

Ugh. More thought required.

Thanks Matt

PS: Always loved your sig line. That sign -- without the exceptions -- hangs on my wall. 🙂 It's generally ignored anyway.