Once upon a time, I was sitting in my office, minding my own business, when this guy walked by, looking for someone, anyone who knows SQL Server. In a rush, he popped his head in and went:
"You don't know anything about temporal tables, do you?"
Me, who automatically heard "temporary tables", made a tired face and blatantly answered yes.
It took me a minute of him explaining the issue he was having until I understood that temporal tables was actually a real thing, which I had never even heard about before. Saving face, I just nodded, gave very general responses and told him I could look into it. He left satisfied and I started googling like crazy, learning all there was to know about temporal tables.
In case you didn't know, temporal tables is a way to save historic data of rows in tables as they were before updates/deletes occurred.
Each table that utilizes temporal tables also creates a new table on it, named "originaltablename_history" with copied data on it, along with a start and end time. When you update a row in the original table, this temporal table will create a new row for that row, but also keep the old one, but putting an end time on the old row and a start time on the new row. Now you can access data that existed in the past! Cool, right?
If I would name this functionality, however, I would call it "anti-GDPR tables".
In case you are dabbling in temporal tables, make extra sure your developers are coding properly, and you won't end up in the same situation as I was in.
What do I mean by coding properly? Well, it turns out that the start time and the end time of temporal table rows are actually determined by the transaction's start time, and not the actual time of the update. So, if you are updating the same row in the same table on two different database sessions on two different database transactions, and one transaction starts before the other one, but the other one actually updates its row before the first one, the first one will try to add a new row in the historic table that has an start time BEFORE the last row's end time. And when this situation arises, SQL Server says "BACK THE HELL UP! Something smells fishy around here!" and it will fail the update and the first transaction will start rolling back. Ouch.
But why would the time be based on the transaction start time, you ask? I don't know. There is probably a good reason for it. Probably. Maybe. I haven't found one so far.
Before we leave, let's just throw an example in here, in case you didn't get all of that...
Now this is a very simple example and in real life, this type of situation probably doesn't arise.
What does arise is much, MUCH worse!
Say your system is running a very large process, handling thousands of invoices with critical data. This process has been running for two hours, because it's just super heavy and could also use some performance tuning, and the entire thing is wrapped inside a transaction. Two hours in, one of your client side users decide to do something on a certain invoice. A click of a button here, a bit of manual overlay there and all of a sudden, your user updates the same row that will be updated by the very large process in about an hour. When that hour passes, the very large process will get this error and rollback your entire now three-hour process.
Very. Sad. Trombone.