August 15, 2016 at 8:55 am
We have a requirement in one of our SQL Server solutions for users to be able to query data from lots of related tables as at any point in time.
SQL Server 2016 comes with built in temporal tables which would give us exactly what we need. Unfortunately the company I am working for only supports SQL Server 2014, with no imminent plans to upgrade.
Has anyone encountered this type of requirement before and got some good advice? Are there any third party tools or SQL scripts that anyone has built that covers this functionality?
I'm assuming that any solution will be using either an audit log type table or individual history tables. It gets really complicated when foreign keys are taken into account.
Any help would be gratefully received.
August 16, 2016 at 3:27 am
I have done this kind of work before. It's somewhat difficult. Basically you have to arrive at a mechanism to version the data as it goes in. I have some examples on an older article here [/url]that describes the basics of how we did it. Basically you have to plan on nothing but inserts, and then have a way to version the data so that, relationally, you can count on getting only the latest from each of the tables. You can do this without updating every table in the data set. The queries & structures in the article reflect that. I hope it helps.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 16, 2016 at 3:57 am
I've done it before as well, using triggers. It's a pain in the neck to get right and keep in sync, and the queries to get the correct point in time aren't trivial.
Is upgrading to SQL 2016 not an option? Since you need a feature in 2016, personally I'd use that to motivate for an upgrade.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply