System versioning in SQL Server

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.

Introduction

First let look at the syntax for a bit.

SELECT	CodeSetId,
		CodeId,
		[Description],
		[Start],
		[End]
FROM dbo.Codes FOR SYSTEM_TIME AS OF '2019-10-03 06:50:00'
Syntax for getting the a specific date

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.

CREATE TABLE dbo.Registrations
(
	Id BIGINT NOT NULL,
    Registration DATETIME2 NOT NULL DEFAULT GETUTCDATE(),
	Tittel VARCHAR(150) NOT NULL,
	Note VARCHAR(MAX),
	CodeSetId INT NOT NULL,
	CodeId VARCHAR(10),
	PRIMARY KEY (Id),
	CONSTRAINT FK_Registration_CodeSetId FOREIGN KEY(CodeSetId) REFERENCES CodeSet(Id)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION,
	CONSTRAINT FK_Registration_CodeId FOREIGN KEY(CodeSetId, CodeId) REFERENCES Codes(CodeSetId, CodeId)
	ON DELETE NO ACTION
	ON UPDATE NO ACTION
);

SELECT	R.*, C.[Description]
FROM	dbo.Codes FOR SYSTEM_TIME ALL C
INNER JOIN dbo.Registrations R ON R.CodeSetId=C.CodeSetId AND R.CodeId=C.CodeId AND R.Registration BETWEEN C.SysStartTime AND C.SysEndTime
Example joining and useing SysEndTime and SysStartTime

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. SysStartTime and 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.

Links

Teis Lindemark

Passionate software developer and beer brewer

Bergen, Norway https://teilin.net