Table Versioning in SQL Server: What is it and how does it work?

The importance of traceability of changes

One of the requests that are frequently made to us in many of the projects that we start is to have traceability of the changes that are made to any table.

For some of the projects, only simple traceability is required to know the date, time of creation and the last update of the table, as well as the users who carried out, said actions.

But in other cases, a complete history of all the changes made is required, for which, a change history table was generated, the implementation of the filling of this table normally had 2 possible options:

• Use the unpopular Triggers that are fired in the database at the row level when an Update/Insert/Delete operation is performed
• Overload the business logic and implement it in the methods where any change is made to the table

Whatever the choice we make between the two previous options, it forces us to incur both an economic and performance cost, which we could save in the vast majority, if we applied one of the many not-so-well-known features that SQL Server has…

Let’s see next what feature we are talking about.

What is System Versioning?

The feature that we want to talk about and show you on this occasion is System Versioning, a feature that automates the generation and maintenance of the Historical Table in a transparent way to the application.

After activating this feature in SQL Server, the History Table is automatically created with the same Schema as the Main Table, and all changes to the Main Table begin to be recorded, generating a row in the History Table automatically each time that any type of update is made on the Main Table.

How do we enable System Versioning on an existing table?

To enable System Versioning in our existing table, we just have to perform the following steps:

Step 1

The first thing is to modify the schema of the Main Table, adding 3 columns that will be used internally to record the effective dates of each row:

alter table dbo.[Contacto]
add
    TimeStart datetime2 generated always as row start not null default getutcdate(),
    TimeEnd datetime2 generated always as row end not null default convert(datetime2, '9999-12-31 23:59:59.9999999'),
    period for system_time (TimeStart, TimeEnd);

Step 2

The next step is to activate the Versioning feature in the Main Table, indicating the name of the History Table to be created.

alter table dbo.[Contacto]  
set (system_versioning = on (HISTORY_TABLE = dbo.[Contacto_History]));

Once the feature is activated, we will see in SSMS (SQL Server Management Studio) a small visual change in the way the Main Table is displayed, as you can see below:

No Versioning	With Versioning

In addition, if we display the Main Table, we will also see the name and the properties of the Historical Table, as you can see in the following image:

And in this simple way, we would already have System Versioning enabled in our Table.

But what actions can this SQL Server feature perform?

System Versioning in action

On the Main Table (Contact) that we have already versioned, we perform the following operations:

Step 1

We insert a new Contact, with name = ‘Versioned Contact’:

insert into Contacto (Nombre, Telefono)  values ('Contacto Versionado',  '+34 666 66 66')

Getting the following row:

Step 2

We wait a few minutes, and then we perform an Update of the phone:

update Contacto set Telefono = '+34 999 99 99' where Id = 1

Step 3

We wait a few more minutes, and we make another Update, this time of the Name:

update Contacto (Nombre, Telefono)  set Nombre = 'Contacto Versionado 2.0' where Id = 1

Step 4

Finally, we examine the contents of both tables:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto where Id = 1

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto_History where Id = 1

As expected, in the Main Table we can see that the information updated after the last Update of action number 3 is kept, while in the History Table we have the previous content of the Main Table to carry out each of the Updates carried out in the actions number 2 and 3.

How to extract information from a given DateTime in SQL Server?

After the System Versioning feature is activated, one of the very useful queries that we can also perform is to obtain the status of a row at a certain past date and time.

To carry out this query, we will use the FOR SYSTEM_TIME clause when performing a SELECT on the Main Table.

Starting from the date and time of execution of the 3 actions, we would have the following table:

Action Number	Description	Date and Time Action
1	Contact Insert	2022-11-28 12:34:36
2	Telephone Update	2022-11-28 12:36:42
3	Name Update	2022-11-28 12:40:54

Now let’s look at a series of examples:

Example 1

We get the version of the Contact, just after executing Action 1 and before Action 2:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:35:00' where Id = 1

Example 2

We get the version of the Contact just after Action 2 and before Action 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

Example 3

And finally, we get the Contact information right after Action 3:

select Id, Nombre, Telefono, TimeStart, TimeEnd from Contacto for system_time as of '2022-11-28 12:37:00' where Id = 1

How to extract historical information in SQL Server?

In addition to the AS OF clause, we could also use the following clauses to query historical information:

• FROM <start_date_time> TO <end_date_time>
• BETWEEN <start_date_time> AND <end_date_time>
• CONTAINED IN (<start_date_time>, <end_date_time>)
• ALL

FROM and BETWEN

FROM and BETWEEN return rows that overlap the specified period.

CONTAINED IN

CONTAINED IN return those rows that fully existed within the period.

ALL

With ALL we would get all the historical rows.

And with this simple SQL Server feature, System Versioning, we can have detailed traceability of all the changes that are made at any time.

In case you want to develop more in-depth the creation of tables, I leave you the following link.

Francisco Valcarreras – Senior Product Owner at Itequia