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.

  • 2850 views

  • Rate
    [Total: 5    Average: 4.6/5]
  • Peter Schott

    It could probably be argued that Always Encrypted has a lot of the old becoming new. It’s always been possible to encrypt data in a column through some means, making it useless to those who don’t have the decryption key. It’s just a lot easier to manage within SQL by granting the appropriate certificate rights to users who need to see it. (And of course, it can be used with straight SQL unlike the external implementations.)

    I’m sure we’ll see some NOSQL arguments here, though. That idea does seem to keep coming back in different forms as a way to get around that pesky relational database. Some implementations are really good, but many times it just seems like people want to do stuff faster and don’t think the whole idea through. That’s why we see NOSQL platforms implementing features from relational systems now. Of course, some of those are because people tried to use the NOSQL platform the way they’ve always used a relational platform. Some of them are good enhancements and optional. However, my favorite was the NOSQL vendor who did a “who are we” presentation and basically said that they would replace Oracle, Microsoft, and IBM with their platform. Sadly, I never really heard what the platform _did_.

  • Keith Rowley

    My favorite current example of old becoming new again is the way car companies are bringing back the muscle car look and feel from the 60s and 70s. 🙂

    On the topic of physical ledgers vs digital ones I must say I REALLY like having a backup of my records. Not sure what a company with only physical ledgers would do about missing records if the accounting department building burned down. If I were committing fraud back then that’s how I would plan to cover my tracks.

  • rogerthat

    Amusing: I am amazed at the resurgence of vinyl media and vacuum tube amplifiers. The affluent
    audiophile has no end of choices that used to be the only choice in listening to music.

    Down to earth: Analysis, Design and Critical Thinking. We are inundated with the latest breakthrough in languages and features. Thankfully, these shiny things do make technical issues simpler to solve, but the technical issues fade away over time; they morph into different challenges. The list of old, outdated technical challenges keeps growing, and their former shiny solutions fade away.

    However, the college day learnings regarding analysis and design, critical thinking, do not lose their value. Yes, it is critical to keep up on the shiny stuff – it is useful, for a time. Remember to think before reaching for the shiny thing, has the real business problem been addressed? Root cause of the issue? Has the shiny thing’s expiration date come up?

  • willliebago

    Instant film cameras, feature phones and fax machines have increased in popularity lately. My favorite is fax machines and their physical security features 🙂