Introdused in Microsoft SQL Server (MsSql) 2016, system versioning was introduced. To check whitch version that is installed, run
SELECT @@VERSION. When I am checking out this neat feature, I am using Sql Server 2017.
Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)
I am creating a brand new database to explore system versioning. Using the structure below. My point here is to see how system versioning can help me keep a set of codes up to date and that some registration created will get the same code that it had on the time of registration. For example, when entering a diagnose on a patiant, there are a lot of codes in a codeset. It is important that one patient in 2000 with diagnose A, that diagnose A do not change context. The codeset may been updated between 2000 and 2019, but for the registration in 2000, I want the context and description and so on from 2000.
First let look at the syntax for a bit.
When using system versioning, the first thing is that the system time is UTC time. The SQL script above will give the codes that was registered at the specific time.
The SQL code above is also interesting and could be useful. When there is a set of data that is used and referenced elsewhere, it might be changes to the data that is referenced. Then it would be nice to be able to query the data to get the referenced codes the way they where on the time of registration. This can be done with a simple join and BETWEEN statement on the system versioning columns.
The way this works is because when enabling system versioning, it is created a table for the table system versioning is enabled for, a history table. When looking into that table, all the columns that is in the original table is in place and two extra.
SysEndTime. Using this two fields in a join as in the above example can give the code for the registration, as it was when the registration found place.