Old is the New New: SQL Server 2016 Learns Ancient Auditing Tricks

The new temporal tables in SQL Server 2016 are interesting, in that they seem a much better way of storing any financial information than conventional relational tables. They have been referred to by some as “SQL Server’s time machine”. They are system-versioned tables that allow SQL Server to maintain the different versions each row, using date columns to track the period for which each value in the row was valid. In short, it allows us to view not only the current data in specific columns, but also what it looked like at any previous point in time.

Before computerization of book-keeping, all financial records were temporal, secure and very difficult to either ‘cook’ or steal. In the zeal to computerize these records, much of this was swept away, deemed unnecessary. Sadder but wiser, we are only recently beginning to take temporal records, audit trail and end-to-end encryption seriously, and putting back the features that were once taken for granted.

It is easy to sneer at the idea of the classical hand-written records in a bound ledger. However, it made forensic work rather easy, and the double-entry bookkeeping techniques guarded against human error. Pacioli’s seminal work on this topic was printed in 1494!

When the ledgers were placed in the safe overnight, you could sleep pretty soundly. You didn’t need to worry about your entire customer base being stolen in a few seconds.

The experience of thousands of years of trading had honed the system well and anyone attempting to manipulate existing data, or delete data, in a ledger, had a tough task. No data in a ledger was deleted. If you made a mistake in your data entry, you simply made a new “contra” entry to rebalance the figures. Each entry in the ledger was sequential so you could be sure of the dates on which data was entered or ‘contra’d’, and exactly how it changed. A ‘missing page’ would be detected immediately. Handwriting made authentication easy. Few databases today have audit trails as tight as this.

So with the zeal applied to anything new and wonderful, SQL Server now offers system-versioned temporal tables. We throw in schema-based security and end-to-end encryption, and finally the data in our database might be almost as hard to copy, steal, or fraudulently manipulate, as the data in a ledger!

Let’s hear your other best examples of where ‘old’ has become the new ‘new’.

Commentary Competition

Enjoyed the topic? Have a relevant anecdote? Disagree with the author? Leave your two cents on this post in the comments below, and our favourite response will win a $50 Amazon gift card. The competition closes two weeks from the date of publication, and the winner will be announced in the next Simple Talk newsletter.